Use a variable in a DLOOKUP?

[leftylee]

I have a form that opens from a button on another form. It includes a field with a DLOOKUP of the record displayed on the first form. It works just fine, but I'd like to open this second form from two different first forms. So I need the forms! reference in the DLOOKUP to be different depending on the form it was opened from. Can I use a variable somehow for this?



Can find my way around Access pretty well but when I get into VB or even SQL, things get complicated for me.

Thanks

[hertfordkc]

Try hiding a text box on your first form. Store the first forms reference there and then build a reference to it in your second form. I've done this in VB, but I think you can do it by building expressions in a fields control source.
Hertfordkc

[leftylee]

Sorry but I don't understand. When I build a reference on the second form to the form from which it was open, won't it have to be forms![formname][fieldname]? If so, how does that help? I need the [formname] to vary depending on which form it was opened from.

[Stanggirlie]

Can you nest the Dlookup in an IF statement? Just a thought.

[leftylee]

Seems like it should. Would the logic be something like "if form1 is open, then do this lookup, else do that lookup"? What expression would I use to test whether a form is opened?

[Stanggirlie]

Okay, I re-read your problem and this is how I understand it...
You have Form1 and Form2. From these two different forms, you have Form3 that can be opened from either Form1 or Form2. And if it's opened from Form1, the DLookup is different then if it's opened from From2, correct?

If that is the issue, I'd make a button on both Form1 and Form2. When you click that button, it does two things in order...
First, it opens Form3
Second, it calculates the DLookup in the manner you want

The button then dictates the correct Dlookup you want. You don't have to worry about which form is open. This can be accomplished thru macros using the conditions. Does that make sense?

[hertfordkc]

Try hiding a text box on your first form. Store the first forms reference there and then build a reference to it in your second form. I've done this in VB, but I think you can do it by building expressions in a fields control source.
Hertfordkc

Sorry, I left out the most important part. From the third form, reference the field using "Parent.HiddenField......."

Hertfordkc

[rpeare]

If SysCmd(acSysCmdGetObjectState, acForm, "<formname1>") = -1 Then
     <perform first dlookup>
elseIf SysCmd(acSysCmdGetObjectState, acForm, "<formname2>") = -1 Then
     <perform second dlookup>
endif

[leftylee]

Rpeare, I think this is what I'm looking for. But where do I put this code? I want the result of the lookup to display in an unbound text box. Is the code the source for the text box?

[rpeare]

you would put it in the ON OPEN property of the each of the first two forms can open.

As I understand it you can have this

FORM 1 ----> Button Click ----> FORM 3
FORM 2 ----> Button Click ----> FORM 3

And you want to pass a variable from either form 1 or form 2 (whichever is currently open) to form 3 so in the ON OPEN property of form 3 you would have to determine which form was open (the getobjectstate portion of the code) and do whatever you want to do based on the result of that if statement. Note I haven't done testing with this but I think it should work.