DLookup

[awake2424]

I have a tblQubit:
1234
5678
9876

Is it possible to use DLookup to pull the Lot in-use:

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

But when that lot changes it only applies to new records and the previous lot remain unchanged?

So if records 1,2,3,4 used lot: 1234

Record 5,6,7,8 lot: 5678

Records 9,10,11,12 lot: 9876

Thank you.

 

[plog]

I think I speak for most people when I say, 'Huh?'

You mentioned your table (tblQubit) and posted some random data of it, but you didn't tell us the field that data was in. Then you mentioned multiple field names that were in your table, but told us nothing of what value they had nor why. You mentioned data changing, but did reference how or why it changed nor how it was applicable to your issue. Also, in the sample data you listed 3 values, but later on you referenced record numbers 4-12.

Try again, this time with field names, more values for each field in your table. Pretend we have no frame of reference about your database, because we don't.

 

[awake2424]

tblQubit
Lot In-Use
1234 yes
5678 no
9 10 11 12 no

I use the code below to pull the Lot from the tblQubit where in-use is yes.

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

This works fine until I change the Lot and another is In-use, then all that populates is the lot that is in-use. Is it possible that the values that were previously there stay and the new lot only applies to new records?

The example data was meant to illustrate when a new lot is put in-use that is the only value that shows, the previous do not. Thank you.

 

[shutzy]

i think i speak for most people on here. we need an insite into you database.

im trying to understand what the numbers are. are they unique id's, are they just a number. what are you trying to do. are you trying to pull a list of lot numbers that are NOT IN USE?

=DLookUp("[Kit]","tblQubit","[Inuse] = -1") seem to imply that you are trying to pull up the kit item of [Kit] id if it is not in use. am i correct in this assumption.

if you could write a brief description of what you are wanting to achieve and the relevant fields that are needed to achieve this.

 

[awake2424]

The lot #'s in tblQubit are unique.

tblQubit
Lot In-use
123 check
456 no check
789 no check

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

pulls the lot # with the check into a form automatically so the user does not have to enter it.

The problem is, when a new lot is in-use that is the only one that appears in all the records on the form. For example:

Record 1,2,3 all had Lot 1,2,3 checked so that lot appeared in the form.

However in record 4 a new lot (456) was put into use and now that lot appears in all the records. (1,2,3,4) instead of only in record 4.

I did not think of this until now and am looking for a way to only apply the current in-use lot to new records. So using the previous example records 1,2,3 would have lot # 123 and only record 4 would have the new in-use lot applied to it. I hope this makes sense. Thank you.

 

[plog]

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

No, not much sense at all. Mostly because you are still referencing a field ([Kit]) that you have given us no information about.

I really can't figure out your issue, so let me explain what your code is doing: It is pulling one Kit field value from tblQubit where the Inuse field is true.

If you have this data:

tblQubit
Kit, Inuse
11, True
13, True
71, False
19, True

and you run your Dlookup--you cannot accurately predict what value you will get back. You may get 11, you may get 13 you may get 19 because all of those records meet your criteria (Inuse = true). My guess is your issue has to do with you not providing enough criteria to your Dlookup.

 

[Pat Hartman]

I think I understand.

tblQubit contains kits (the listed numbers) only ONE of which may be marked as InUse = True at any point in time. On your form, you are displaying the Kit that is currently flagged as InUse but that may not be the kit that was in use when the record was created. Nod your head.

The solution is to actually store the value of Kit rather than just displaying it. To do that, you'll need to add a Kit column to whatever table we're talking about. Then in your form, you'll need to add code to pull up the actual Kit number. I would use the on Dirty event so that Kit gets populated as soon as someone starts typing. But, the code will need to only apply to new records since you probably don't want to change the Kit value if you change something else on the record. Or do you?

If Me.NewRecord Then
    Me.txtKit = DLookUp("[Kit]","tblQubit","[Inuse] = -1") 
End If