Troubleshoot Dlookup

[Gavx]

On the main form is a combo box (cboProductID). The user selects an item in the combo box and an SQL Insert statement inserts the item into a subform of the main form.

I am trying to determine the ProductTypeID of the item that has been inserted into the subform and am trying to use this statement but it is not working and I believe the problem is with how I am referring to the cboProductID.

Here is the statement that is not working;

iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID = " & Forms.Parent.cboProductID.Value)
(this is in the After_Update event) of one field of the inserted record.

In attempting to solve the problem I replicated it in SQL;

Expr1: DLookUp("ProductTypeID","tblProduct","ProductID = Forms![frmBooking]![cboProductID].[value]")

And this works but unfortunately doesn't help me with iProdType declaration.
What do you think the problem could be with the declaration statement?

 

[pbaldy]

Have you tried using frmBooking instead of Parent, like you did in the working version?

Forms![frmBooking]![cboProductID]

 

[Gavx]

Took your suggestion;

iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID = " & forms![frmBooking]![cboProductID]

But the statement highlights in red - the syntax is not correct

 

[pbaldy]

You left off the closing parentheses.

 

[Gavx]

Adding the bracket corrected it but the statement still is not reporting a result.

 

[Solo712]

Adding the bracket corrected it but the statement still is not reporting a result.

Are you requerying the subform after the insert ?

Again, at the risk of repeating myself, the subform's recordset will not know about the inserted record before you requery the subform.

Best,
Jiri

 

[pbaldy]

Are you doing something with the variable? Can you post the db here?

 

[Gavx]

Here is teh statement now;

iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID = Forms![frmBooking]![cboProductID].[Value]")

If I paste
?DLookup("ProductTypeID", "tblProduct", "ProductID = Forms![frmBooking]![cboProductID].[Value]")

into the immediate window I get the result I am after.

If I paste
?iProdType
into the intermediate window I don't an answer.

With the variable I am giving the following statement;

Me.txtDateOut.Enabled = Nz(iProdType, " ") = "4"

I will see if I can compress the db and post.

 

[Gavx]

More troubleshooting.

Into the watch window I have pasted;
DLookup("ProductTypeID", "tblProduct", "ProductID = Forms![frmBooking]![cboProductID].[Value]")

and in the Type column it is listed as Variant/Long.

Forms![frmBooking]![cboProductID].[Value]
is reported as a String in the type column.

Bear in mind that iProdType is declared as integer.

And iProdType is in the Watch window listed as a value "Expression not defined in context"

Does any of this help?

 

[gemma-the-husky]

As an alternative simply put the producttypeid into the combo box. Then you can get it directly -mycbo.column(x) and you don't need a dlookup.

 

[Solo712]

Again, at the risk of repeating myself, the subform's recordset will not know about the inserted record before you requery the subform.

Best,
Jiri

Did you see this ?

Jiri

 

[Gavx]

I tried this immediately after it was suggested but it had no effect.

I rationalised because the After_Update occurs on a field that has been inserted by the SQL.:banghead:

 

[Solo712]

I tried this immediately after it was suggested but it had no effect.

I rationalised because the After_Update occurs on a field that has been inserted by the SQL.:banghead:

I think you are missing some big pieces here, Gavx. The SQL Insert will not cause the After_Update to fire in the subform. The SQL statement will update the table but the recordset of the subform will not know that until you requery the subform and update its recordset. The Before and After Updates, Before and After Inserts are only functioning for table updates done through the form's own recordset. Set up a break in the After_Update event and see if the code ever gets there.

You should have the subform requery statement right after executing the SQL command (I imagine it sits in the On_click event of the cbo, in the main form). Now, you could extract your "iProdType" variable right there and then and then figure out how to trigger an event where it would be picked up by the subform for processing if it is needed there. One way to do that would be setting up an OnTimer event in the subform which would look for a non-NZ value in the iProdType global variable or a TempVar.

Best I can do my friend