lookup VBA using one table or more?

[awake2424]

I am creating a lookup on a form that will pull a value only if a column (Inuse) is true. Is it better to have seperate tables for each condition (say 1 and 2) or 1 table with conditions (1 and 2) in it? If one table is preferred does the below look correct? Thank you .

Separate

Me.Text61=DLookup("[Kit]","[tblExtract1]","[Inuse]=-1")
Me.Text62=DLookup("[Lot]","[tblExtract1]","[Inuse]=-1")
Me.Text63=DLookup("[Kit]","[tblExtract2]","[Inuse]=-1")
Me.Text64=DLookup("[Lot]","[tblExtract2]","[Inuse]=-1")

One table

Me.Text61=DLookup("[Kit]=1","[tblReagent]","[Inuse]=-1")
Me.Text62=DLookup("[Lot]=1","[tblReagent]","[Inuse]=-1")
Me.Text63=DLookup("[Kit]=2","[tblReagent]","[Inuse]=-1")
Me.Text64=DLookup("[Lot]=2","[tblReagent]","[Inuse]=-1")

 

[sneuberg]

It would help if you explained more about what you are actually doing as I wonder why you would use DLookups rather than just bind the table to the form .

Without know what this data really is I'm going to vote for a single table but it wouldn't be set up the way you have it. We need a field to distinguish the data in place of having the tables tblExtract1 and tblExtract2. So let call it Extract and say that if Extract equals 1 then it would be like tblExtract1 and if equal to 2 then like tblExtract2. Let's also assume the single table is named tblReagent] then the DLookups would look like:

Me.Text61=DLookup("[Kit]","[tblReagent]","[Inuse]=-1 And [Extract] = 1")
Me.Text62=DLookup("[Lot]","[tblReagent]","[Inuse]=-1 And [Extract] = 1")
Me.Text63=DLookup("[Kit]","[tblReagent]","[Inuse]=-1 And [Extract] = 2")
Me.Text64=DLookup("[Lot]","[tblReagent]","[Inuse]=-1 And [Extract] = 2")

 

[awake2424]

So basically what I am trying to do is create 1 table with multiple reagents in it and have a field on that table that distinguishes each. Then in a form have that lookup table be used to populate the form. Like you have in your example.

Using your example if

Type=1

the code works great. However, if

Type= string value

like in the example below, the string is not avaled search term.

Me.Text663 = DLookup("[Kit]", "[tblReagent]", "[Current] = -1 And [Type] = TE")

Can a string not be used?

Thank you .

 

[sneuberg]

It would need to be enclosed in single quotes like

Me.Text663 = DLookup("[Kit]", "[tblReagent]", "[Current] = -1 And [Type] = 'TE'")

Also if Current is a Yes/No field you can use True like

Me.Text663 = DLookup("[Kit]", "[tblReagent]", "[Current] = True  And [Type] = 'TE'")

 

[awake2424]

Very helpful.... thank you.

On the lookup table which is nothing more then the below:

Kit
Lot
Type

I have it setup to where kit is a dropdown of defined values. Is there a way to have "Kit and Type" linked, so that when the user selects the "Kit", "Type" is changed/updated to that same value? I tried the below query to do so, but the "Type" is empty and not changed.

select query in theType field

SELECT tblReagent.Type FROM tblReagent WHERE (((tblReagent.Type)="Kit"));

Also tried: ((same result)

UPDATE tblReagent Set Kit=Kit Where Type=Kit

Thank you .

 

[sneuberg]

Something sounds like it not normalized correctly. I don't believe you should have to update type to the same value. If Kit determines the Type via the lookup table then Type doesn't belong in the tblReagent table. You can alway get the value of Type by joining the lookup table with the tblReagent table. Or maybe there is something about your system I don't understand. Could you upload your database so I could get a better understanding?

 

[awake2424]

I will post the db tomorrow, but the table structure is.

TblName

Kit Lot Recdate QCdate Usedate Expdate Current Type

Basically, when the user selects kit that same selection appears in Type as well.

The form that is populated with these values works great using your suggestion.

For example,
Kit is sample, so Type is sample. Thank you for all of your help .

 

[sneuberg]

If Type is always the same as Kit and you just want to view them bu different names then choose to have one of them stored in the table let's say Kit for example and then Type would be just Type: [Kit] in queries or =[Kit] in the control source of a textbox on a form.

If Type is not alway the same as Kit but usually is and you want to default it's value to whatever is ever Kit is then when Kit is entered, then put something like this into afterupdate event of the Kit combo box.

CurrentDb.Execute "UPDATE tblReagent SET Kit = '" & Me.Kit & "' WHERE Type = '" & Me.Kit & "'", dbFailOnError

Here I'm assuming the combo boxes' name for the Kit is Kit.

 

[awake2424]

The below is the format of my table.

tblReagent

Kit  Lot  RecieveDate QCDate	InuseDate	ExpirationDate Current  Type

If I create an update query for type and set the updateto to [Kit] and run it manually the table updates. Is there a way to run the query after the user updates Kit in the table? I guess similar to AfterUpdate on a form but since this a table I am not sure. Or is there a better way to update type when kit is entered? Thank you .

 

[sneuberg]

The below is the format of my table.

tblReagent

Kit  Lot  RecieveDate QCDate	InuseDate	ExpirationDate Current  Type

If I create an update query for type and set the updateto to [Kit] and run it manually the table updates. Is there a way to run the query after the user updates Kit in the table? I guess similar to AfterUpdate on a form but since this a table I am not sure. Or is there a better way to update type when kit is entered? Thank you .

If Type is always the same as Kit, which seem to be the case as you want a table level update, and you really need this Type field, then I suggest making Type a calculated field. Open the table in design view (See attached screen shot) and choose Calculated as the field type. The expression for Type is simply [Kit]. You can look at the attached database also to see how this is set up and works.

 

[awake2424]

I get this error in access 2013 on the field in the table

Invalid SQL syntax - can not use multiple columns in a column- level check restraint.


Expression [Kit]

Thank you .

 

[sneuberg]

I have Access 2013 also. Is it working in the sample database I sent on your system?

You have to delete the existing Type field and then readd it. Otherwise I don't know what the problem might be. Could you upload your database?

 

[awake2424]

The database was designed in access 2003 so I updated it to the newer version and everything works great. Thank you for all your help .