check data before updating table tina 11/19/2008 10:26:00 AM Hi I have a form based on table which requires user to enter stock code and lot number and quantity against bin location alreay in table . before data is entered i would like to check that the lot number is valid against stock code ie appears against stock code in another table stock code and lot number are both strings . I am struggling on where to start Any help would be great Thanks Tina

Re: check data before updating table "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> 11/19/2008 12:13:51 PM Create an BeforeUpdate event in the control. You can use the DLOOKUP() function to look it up in the other table. Something like: Dim test string test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me.LotNumber "') If isnull(test) Then MsgBox - your lot number is wrong Me.LotNumber.SetFocus End If Bonnie http://www.dataplus-svc.com tina wrote: [Quoted Text] >Hi >I have a form based on table which requires user to enter stock code and lot >number and quantity against bin location alreay in table . before data is >entered i would like to check that the lot number is valid against stock code >ie appears against stock code in another table stock code and lot number are >both strings . >I am struggling on where to start >Any help would be great >Thanks >Tina -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: check data before updating table tina 11/19/2008 1:27:01 PM Hi Thank you I am sure this will work except the code has 2 open brackets and 1 close bracket i have tried to enter extra bracket but can't get it in right place to be accepted Tina "bhicks11 via AccessMonster.com" wrote: [Quoted Text] > Create an BeforeUpdate event in the control. You can use the DLOOKUP() > function to look it up in the other table. Something like: > > Dim test string > > test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me.LotNumber > "') > > If isnull(test) Then > MsgBox - your lot number is wrong > Me.LotNumber.SetFocus > End If > > Bonnie > http://www.dataplus-svc.com > > tina wrote: > >Hi > >I have a form based on table which requires user to enter stock code and lot > >number and quantity against bin location alreay in table . before data is > >entered i would like to check that the lot number is valid against stock code > >ie appears against stock code in another table stock code and lot number are > >both strings . > >I am struggling on where to start > >Any help would be great > >Thanks > >Tina > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 > >

Re: check data before updating table "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> 11/19/2008 1:52:14 PM test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. LotNumber"')) Bonnie http://www.dataplus-svc.com tina wrote: [Quoted Text] >Hi >Thank you I am sure this will work except the code has 2 open brackets and 1 >close bracket i have tried to enter extra bracket but can't get it in right >place to be accepted >Tina > >> Create an BeforeUpdate event in the control. You can use the DLOOKUP() >> function to look it up in the other table. Something like: >[quoted text clipped - 22 lines] >> >Thanks >> >Tina -- Message posted via http://www.accessmonster.com

Re: check data before updating table "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/19/2008 2:41:21 PM I think that needs to be test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me.LotNumber & "')") although there really is no need for the superfluous parentheses: test = DLookup("[StockCode]", "[TheTable]", "[StockCode] = '" & Me.LotNumber & "'") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> wrote in message news:8d6c83972435a[ at ]uwe... [Quoted Text] > test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. > LotNumber"')) > > Bonnie > http://www.dataplus-svc.com > > > tina wrote: >>Hi >>Thank you I am sure this will work except the code has 2 open brackets and >>1 >>close bracket i have tried to enter extra bracket but can't get it in >>right >>place to be accepted >>Tina >> >>> Create an BeforeUpdate event in the control. You can use the DLOOKUP() >>> function to look it up in the other table. Something like: >>[quoted text clipped - 22 lines] >>> >Thanks >>> >Tina > > -- > Message posted via http://www.accessmonster.com >

Re: check data before updating table "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> 11/19/2008 3:02:48 PM Thank you Douglas. Bonnie Douglas J. Steele wrote: [Quoted Text] >I think that needs to be > >test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & >Me.LotNumber & "')") > >although there really is no need for the superfluous parentheses: > >test = DLookup("[StockCode]", "[TheTable]", "[StockCode] = '" & Me.LotNumber >& "'") > >> test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. >> LotNumber"')) >[quoted text clipped - 15 lines] >>>> >Thanks >>>> >Tina -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: check data before updating table tina 11/19/2008 3:09:01 PM Hi Sorry to be a pain but this still has syntac error Private Sub LotNumber_BeforeUpdatePrivate Sub LotJob_BeforeUpdate(Cancel As Integer) Dim test As String test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me. LotNumber"')) If IsNull(test) Then MsgBox " your lot number is wrong" Me.LotNumber.SetFocus End If End Sub where table name is detail do I need to declare table names etc Thanks Tina "bhicks11 via AccessMonster.com" wrote: [Quoted Text] > test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. > LotNumber"')) > > Bonnie > http://www.dataplus-svc.com > > > tina wrote: > >Hi > >Thank you I am sure this will work except the code has 2 open brackets and 1 > >close bracket i have tried to enter extra bracket but can't get it in right > >place to be accepted > >Tina > > > >> Create an BeforeUpdate event in the control. You can use the DLOOKUP() > >> function to look it up in the other table. Something like: > >[quoted text clipped - 22 lines] > >> >Thanks > >> >Tina > > -- > Message posted via http://www.accessmonster.com > >

Re: check data before updating table "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> 11/19/2008 3:22:59 PM See what Doug said above. It should be: test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me.>LotNumber & "')") Mia culpa. Bonnie http://www.dataplus-svc.com tina wrote: [Quoted Text] >Hi >Sorry to be a pain but this still has syntac error > >Private Sub LotNumber_BeforeUpdatePrivate Sub LotJob_BeforeUpdate(Cancel As >Integer) >Dim test As String >test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me. >LotNumber"')) >If IsNull(test) Then > MsgBox " your lot number is wrong" > Me.LotNumber.SetFocus >End If >End Sub >where table name is detail do I need to declare table names etc >Thanks >Tina > >> test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. >> LotNumber"')) >[quoted text clipped - 13 lines] >> >> >Thanks >> >> >Tina -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: check data before updating table tina 11/19/2008 4:38:25 PM Thank you Bonnie Although now I am getting a invaid use of null error the stockcode in detail is an imported table an sql database where the field length is 30 but only 6 characters used when entering in form enter only 6 characters will this cause test to be "" Tina "bhicks11 via AccessMonster.com" wrote: [Quoted Text] > See what Doug said above. It should be: > > test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me.>LotNumber > & "')") > > Mia culpa. > > Bonnie > http://www.dataplus-svc.com > > tina wrote: > >Hi > >Sorry to be a pain but this still has syntac error > > > >Private Sub LotNumber_BeforeUpdatePrivate Sub LotJob_BeforeUpdate(Cancel As > >Integer) > >Dim test As String > >test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me. > >LotNumber"')) > >If IsNull(test) Then > > MsgBox " your lot number is wrong" > > Me.LotNumber.SetFocus > >End If > >End Sub > >where table name is detail do I need to declare table names etc > >Thanks > >Tina > > > >> test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. > >> LotNumber"')) > >[quoted text clipped - 13 lines] > >> >> >Thanks > >> >> >Tina > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 > >

Re: check data before updating table "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> 11/19/2008 5:31:58 PM Sorry Tina, I don't understand the question. Can you clarify what you mean. Bonnie http://www.dataplus-svc.com tina wrote: [Quoted Text] >Thank you Bonnie >Although now I am getting a invaid use of null error >the stockcode in detail is an imported table an sql database where the field >length is 30 but only 6 characters used when entering in form enter only 6 >characters will this cause test to be "" >Tina > >> See what Doug said above. It should be: >> >[quoted text clipped - 28 lines] >> >> >> >Thanks >> >> >> >Tina -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1

Re: check data before updating table "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/19/2008 5:40:19 PM Try test = DLookup("[StockCode]", "[detail]", "[StockCode] Like '" & Me.LotNumber& "*'") or test = DLookup("[StockCode]", "[detail]", "Trim([StockCode]) = '" & Me.LotNumber& "'") -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tina" <tina[ at ]discussions.microsoft.com> wrote in message news:E164893E-970C-4122-BC8F-E584118DD310[ at ]microsoft.com... [Quoted Text] > Thank you Bonnie > Although now I am getting a invaid use of null error > the stockcode in detail is an imported table an sql database where the > field > length is 30 but only 6 characters used when entering in form enter only 6 > characters will this cause test to be "" > Tina > "bhicks11 via AccessMonster.com" wrote: > >> See what Doug said above. It should be: >> >> test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & >> Me.>LotNumber >> & "')") >> >> Mia culpa. >> >> Bonnie >> http://www.dataplus-svc.com >> >> tina wrote: >> >Hi >> >Sorry to be a pain but this still has syntac error >> > >> >Private Sub LotNumber_BeforeUpdatePrivate Sub LotJob_BeforeUpdate(Cancel >> >As >> >Integer) >> >Dim test As String >> >test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me. >> >LotNumber"')) >> >If IsNull(test) Then >> > MsgBox " your lot number is wrong" >> > Me.LotNumber.SetFocus >> >End If >> >End Sub >> >where table name is detail do I need to declare table names etc >> >Thanks >> >Tina >> > >> >> test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. >> >> LotNumber"')) >> >[quoted text clipped - 13 lines] >> >> >> >Thanks >> >> >> >Tina >> >> -- >> Message posted via AccessMonster.com >> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 >> >>

Re: check data before updating table tina 11/20/2008 9:52:03 AM Hi Douglas Thank you fro trim suggestion but I am still getting same error when in debug test ="" StockCode is "010464" as entered Lotnumber is 1234 Am I right in thinking dlookup formula is looking up stockcode entered in table detail and seeing if lotnumber matches a lotnumber in a record with entered stockcode and if can't find returns null I think this is failing at finding stockcode in detail table but I donot know why Regards Tina "Douglas J. Steele" wrote: [Quoted Text] > Try > > test = DLookup("[StockCode]", "[detail]", "[StockCode] Like '" & > Me.LotNumber& "*'") > > or > > test = DLookup("[StockCode]", "[detail]", "Trim([StockCode]) = '" & > Me.LotNumber& "'") > > > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "tina" <tina[ at ]discussions.microsoft.com> wrote in message > news:E164893E-970C-4122-BC8F-E584118DD310[ at ]microsoft.com... > > Thank you Bonnie > > Although now I am getting a invaid use of null error > > the stockcode in detail is an imported table an sql database where the > > field > > length is 30 but only 6 characters used when entering in form enter only 6 > > characters will this cause test to be "" > > Tina > > "bhicks11 via AccessMonster.com" wrote: > > > >> See what Doug said above. It should be: > >> > >> test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & > >> Me.>LotNumber > >> & "')") > >> > >> Mia culpa. > >> > >> Bonnie > >> http://www.dataplus-svc.com > >> > >> tina wrote: > >> >Hi > >> >Sorry to be a pain but this still has syntac error > >> > > >> >Private Sub LotNumber_BeforeUpdatePrivate Sub LotJob_BeforeUpdate(Cancel > >> >As > >> >Integer) > >> >Dim test As String > >> >test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me. > >> >LotNumber"')) > >> >If IsNull(test) Then > >> > MsgBox " your lot number is wrong" > >> > Me.LotNumber.SetFocus > >> >End If > >> >End Sub > >> >where table name is detail do I need to declare table names etc > >> >Thanks > >> >Tina > >> > > >> >> test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & Me. > >> >> LotNumber"')) > >> >[quoted text clipped - 13 lines] > >> >> >> >Thanks > >> >> >> >Tina > >> > >> -- > >> Message posted via AccessMonster.com > >> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 > >> > >> > > >

Re: check data before updating table "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/20/2008 12:20:55 PM Yes, you will get a Null. See my answer to your other thread. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "tina" <tina[ at ]discussions.microsoft.com> wrote in message news:39569313-C6BD-4BBC-A212-5913D601EB5B[ at ]microsoft.com... [Quoted Text] > Hi Douglas > Thank you fro trim suggestion but I am still getting same error when in > debug test ="" StockCode is "010464" as entered Lotnumber is 1234 > > Am I right in thinking dlookup formula is looking up stockcode entered in > table detail and seeing if lotnumber matches a lotnumber in a record with > entered stockcode and if can't find returns null I think this is failing > at > finding stockcode in detail table but I donot know why > Regards > Tina > > "Douglas J. Steele" wrote: > >> Try >> >> test = DLookup("[StockCode]", "[detail]", "[StockCode] Like '" & >> Me.LotNumber& "*'") >> >> or >> >> test = DLookup("[StockCode]", "[detail]", "Trim([StockCode]) = '" & >> Me.LotNumber& "'") >> >> >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> >> "tina" <tina[ at ]discussions.microsoft.com> wrote in message >> news:E164893E-970C-4122-BC8F-E584118DD310[ at ]microsoft.com... >> > Thank you Bonnie >> > Although now I am getting a invaid use of null error >> > the stockcode in detail is an imported table an sql database where the >> > field >> > length is 30 but only 6 characters used when entering in form enter >> > only 6 >> > characters will this cause test to be "" >> > Tina >> > "bhicks11 via AccessMonster.com" wrote: >> > >> >> See what Doug said above. It should be: >> >> >> >> test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & >> >> Me.>LotNumber >> >> & "')") >> >> >> >> Mia culpa. >> >> >> >> Bonnie >> >> http://www.dataplus-svc.com >> >> >> >> tina wrote: >> >> >Hi >> >> >Sorry to be a pain but this still has syntac error >> >> > >> >> >Private Sub LotNumber_BeforeUpdatePrivate Sub >> >> >LotJob_BeforeUpdate(Cancel >> >> >As >> >> >Integer) >> >> >Dim test As String >> >> >test = DLookup("[StockCode]", "[detail]", "([StockCode] = '" & Me. >> >> >LotNumber"')) >> >> >If IsNull(test) Then >> >> > MsgBox " your lot number is wrong" >> >> > Me.LotNumber.SetFocus >> >> >End If >> >> >End Sub >> >> >where table name is detail do I need to declare table names etc >> >> >Thanks >> >> >Tina >> >> > >> >> >> test = DLookup("[StockCode]", "[TheTable]", "([StockCode] = '" & >> >> >> Me. >> >> >> LotNumber"')) >> >> >[quoted text clipped - 13 lines] >> >> >> >> >Thanks >> >> >> >> >Tina >> >> >> >> -- >> >> Message posted via AccessMonster.com >> >> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200811/1 >> >> >> >> >> >> >>

Re: check data before updating table "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> 11/20/2008 12:56:51 PM Looks like Tina needs to check her data. Can you direct me to the other thread please? I would like to see your answer. Thanks, Bonnie http://www.dataplus-svc.com Douglas J. Steele wrote: [Quoted Text] >Yes, you will get a Null. See my answer to your other thread. > >> Hi Douglas >> Thank you fro trim suggestion but I am still getting same error when in >[quoted text clipped - 63 lines] >>> >> >> >> >Thanks >>> >> >> >> >Tina -- Message posted via http://www.accessmonster.com

Re: check data before updating table "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/20/2008 1:56:41 PM "simple dlookup" -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "bhicks11 via AccessMonster.com" <u44327[ at ]uwe> wrote in message news:8d789a8c191d3[ at ]uwe... [Quoted Text] > Looks like Tina needs to check her data. > > Can you direct me to the other thread please? I would like to see your > answer. > > Thanks, > > Bonnie > http://www.dataplus-svc.com > > Douglas J. Steele wrote: >>Yes, you will get a Null. See my answer to your other thread. >> >>> Hi Douglas >>> Thank you fro trim suggestion but I am still getting same error when in >>[quoted text clipped - 63 lines] >>>> >> >> >> >Thanks >>>> >> >> >> >Tina > > -- > Message posted via http://www.accessmonster.com >