afterupdate select statement

[awake2424]

I have table (tblTest):
Kit Lot InUse
Amp 123 Yes
Amp 456 No

On a form I have:

Kit
Lot

What I would like to do is create an AfterUpdate select statement on the form that pulls the Kit in the table where Inuse = Yes:

Private Sub Text245_AfterUpdate()
If Me.Text225 = "Test" Or Me.Text225 = "Test2" Then
Me.Text245 = SELECT (Kit, tblTest, [Inuse = -1])
End Sub

but I do not no if that is possible? Thanks.

 

[pbaldy]

You can't do that. Try a DLookup().

 

[awake2424]

The problem with DLookup is that it only pulls 1 value and replaces the previous values. I think I need to use a select statement and was trying to avoid using a combo-box, but that may be the only way.

 

[pbaldy]

You can't have SQL in a textbox, only a combo or listbox as the row source. You could open a recordset on the SQL and use code to populate the textbox.

 

[David R]

Maybe you should explain in plain English what you are trying to do and why you need multiple values pulled to do it. Your original post only asked for how to get Kit In use.

 

[awake2424]

I need to be able to track what kit was used with what sample, but I would like to have the current lot in-use be automatically populated to reduce error.


So, if I have table (tblTest):
Kit Lot InUse
Amp 123 Yes
Amp 456 No

On a form:
Case 1
Kit Amp
Lot 123

If on tblTest the Inuse changes:
Case 2
Kit Amp
Lot 456

Private Sub Text245_AfterUpdate()
If Me.Text225 = "Test" Or Me.Text225 = "Test2" Then
Me.Text245 = SELECT (Kit, tblTest, [Inuse = -1])
End Sub

maybe something like this, but I don't think it will work. I hope that makes sense. Thank you.

 

[David R]

So you want to be able to change which Kit is marked [InUse]=True based on what the user does in the form? Or the other way around (default to pulling the Kit where [InUse]=True onto your form for the user)?

And where does the need for multiple fields come from?

 

[awake2424]

The default of pulling the Kit where [InUse]=True onto your form for the user, the multiple fields are rows on the table. As there are going to be several kits on tblTest, but only one row will be [Inuse]=True. This is the one to pull.

Example: tblTest
Kit Lot Inuse
Amp 123 Yes
Amp 456 No
Amp 789 No
Amp 099 No
Amp 100 No

So, Kit=Amp, Lot= 123. Thank you.

 

[David R]

You don't need to pull multiple rows, DLookup() will find the one that has [InUse] = Yes. Note the change in brackets - you can test this in the VBA immediate window.

Otherwise we're still misunderstanding your problem. Although I will say part it is probably the event you're using....AfterUpdate of 245 to update 245? You'll get yourself in a loop fast as a rabbit! Try afterupdate of 225 I guess...

 

[awake2424]

I use DLookup now

 =DLookUp("[Kit]","tblExtraction","[Inuse] = -1")

The problem is that it applies the value set to Inuse to every record on the form, even if a different kit was used.

Currently, with DLookup if Kit 123 is Inuse=True, then on the form all 5 records have that kit 123. If kit 456 is put in use the previous 5 records say 456 as well as any new records. I need to be able to track what kit was used with what sample, but I would like to have the current lot in-use be automatically populated to reduce error. Thanks.

 

[David R]

Is your textbox bound to a field? It sounds like you're directly using the Dlookup as your recordsource.

 

[awake2424]

I set the DLookup in an unbound textbox and the formula appears as the control source. Is there another way to do it so that it doesn't apply the DLookup to all the records on the form? Thanks.

 

[David R]

You have to store the Kit as a field in your table, otherwise an unbound control will do exactly what you're seeing now.

Do you have such a field set up already? If so, set that as your control source. If not, make one.

 

[awake2424]

How do I query against that table for the current kit Inuse?

I have the form setup as a combo box, Row source is tblTest and Row source type is Table/query.


Example: tblTest
Kit Lot Inuse
Amp 123 Yes
Amp 456 No
Amp 789 No
Amp 099 No
Amp 100 No

So, Kit=Amp, Lot= 123. Thank you.

 

[David R]

Your DLookup posted above will work for FINDING the value you want. You just need to STORE it to the field (probably through a bound field on the form, that will be easiest).

 

[awake2424]

I have attached an example database that I hope helps to illustrate the idea.

I need to be able to track what kit was used with what sample, but I would like to have the current lot in-use be automatically populated to reduce error.

So, if I have table (tblTest):
Kit Lot InUse
Amp 123 Yes
Amp 456 No

On a form:
Case 1
Kit Amp
Lot 123

If on tblTest the Inuse changes:
Case 2
Kit Amp
Lot 456
Thank you for your help.

 

[David R]

aircode:

Private Sub Text225_AfterUpdate()
      If Me.Text225 = "Test" Or Me.Text225 = "Test2" And Me.Text245 = "" Then
      Me.Text245 = DLookUp("[Kit]","[tblExtraction]","[Inuse] = -1")
End Sub