Using result of a dlookup...

[cjmitton]

I'm try to get the result of a Dlookup below so I can use the result later.

In debugging the code. I have made sure that the P3rdBUSID and MatterID have both got the correct Values expected.

Its just that strContIDLookup does not get the result assigned to it?
Am I doing something completely wrong?

StrContIDLookup = DLookup("MA_3rd_P_C_ID", "qry_matters_list_add_3rd_P_Bus_Generic", "MA_3rd_P_B_ID = '" & P3rdBUSID & "' and MA_M_ID = '" & MatterID & "'")

I've tried to use SQL to run the query but my knowledge of SQL in a VBA subroutine is zero, when I run the query as a Access Query and I get the correct result (although I've manually added the relevent numbers in)

SELECT MA_3rd_P_C_ID From qry_matters_list_add_3rd_P_Bus_Generic Where (((MA_3rd_P_B_ID)=38)) and (((MA_M_ID)=11)

Is there a way of doing this with the SQL instead of Dlookup? getting lost at the moment!

Thanks

 

[vbaInet]

It would be useful to tell us EXACTLY in what event you are running the DLookup() code?

 

[cjmitton]

The Code is running when I click a button.

The code its self is part of a much larger subroutine. The Subroutine purpose is to generate a letter (using words mailmerge function) then saving it.

This part of the code comes at the very beginning, to run my 'merge' sqlstatement I need certain values from a previous form. One is carried forward from the form using openargs (this comes from a listbox), another is obtained from the previous form (which is still visable but in the background) the 3rd Value come from the listbox on the previous form (the same as the first value). I hope that madfe sense!

As I can not carry forward 2 values from my list box I decided to create a query where I can use the two values I have available to find the third. then use it where required.

I hope that made sense, you'll have to forgive me if I've messed up any terminology.

 

[vbaInet]

Put this in the Control Source of the textbox and see what happens:

= DLookup("MA_3rd_P_C_ID", "qry_matters_list_add_3rd_P_Bus_Generic", "MA_3rd_P_B_ID = '" & P3rdBUSID & "' and MA_M_ID = '" & MatterID & "'")

 

[cjmitton]

Thanks, That got me heading in a different direction. On the form I run the merge from I created 3 text boxes, got the 2 values in a couple of them then used the 3rd to do the dlookup, changed the code to look at the text boxes for the values and it worked. I then referenced that text box with the dlookup in my code and it worked like a dream. I'll now hide the boxes and jobs done! thanks for that.

 

[vbaInet]

Good job cjmitton!

By the way, if those two values are coming from a field, you can also just reference the field.

 

[cjmitton]

Thanks, I've done that part before but everything was on the same form so I knew a dlookup would work. In this case, 1 value was carried forward as a openarg the other was in a list box thats been selected so I had no way of referencing it.

you got me thinking in a different way though which got me out of that rut! thanks once more

 

[vbaInet]

There's another way too, by referencing a function that returns either of the values.

Happy developing!