Dlookup for another table not working with Recordset properties on same form

[mba_110]

Hi

I have following code to do some functionality for save button on form to get records associated with my combo box.

Private Sub cboPlateNo_AfterUpdate()
Me.Recordset.FindFirst "PlateNo = " & Nz(cboPlateNo, 0)

Now the issue is i have to do DLookup for other table based on combo box value on same form, since form is bind to one table Dlookup for another table is not showing results in text boxes.


for example i have tblVehicleInsurance which is bound to form and i have tblVehicle from which i need some records to show on form relationship in both is [PlateNo].

Can you tell me why Dlookup is not working and how to fix this problem.

 

[GinaWhipp]

Hmm, sounds like you need to requery the control that holds the DLookup(). Though it does seem strange it does requery once cboPlateNo gets updated. It would help if you posted the DLookup statement.

 

[mba_110]

=DLookUp("[Color]","[tblVehicles]","[PlateNo]='" & [cboPlateNo] & "'")

 

[GinaWhipp]

Hmm, well nothing wrong there. I try a requery of that control after your Me.Recordset line.

 

[GinaWhipp]

Just had a thought is the first column in the combo box text or numeric?

 

[mba_110]

Dlookup code is perfect their is no issues checked it on another form , and its text field sir [PlateNo]

 

[Gasman]

So I would ask, what is the value of cboPlateNo when you do the DLookup ?

 

[missinglinq]

The following

Me.Recordset.FindFirst "PlateNo = " & Nz(cboPlateNo, 0)

is the correct syntax if PlateNo is defined as a Number.

But

=DLookUp("[Color]","[tblVehicles]","[PlateNo]='" & [cboPlateNo] & "'")

is correct syntax if PlateNo is defined as Text.

It can't be both...which is it?

Linq ;0)>

 

[GinaWhipp]

Then, as missinglinq pointed out, is the reason for the issue. You need to adjust the Me.Recordset line.

 

[mba_110]

As i mentioned before their is two different tables are used in this event.

tblVehicleInsurance is used for form Record Source, and having PK in Numeric value which is autonumber.

tblVehicle is used to Dlookup vehicle details based on PlateNo which is PK text value.

Now PK of tblVehicle is in relationship with tblVehicleInsurance FK.

tblVehicleInsurance is ok with recordset as its record Source of the form.

tblVehicles
PlateNo [PK] its a text field
UserID [FK]

tblVehicleInsurance
VehicleInsuranceID [PK] its a numeric field
PlateNo [FK]


Now only the problem Dlookup because PK is not numeric in plateNo its a text field, i hope i make thing more clear.

 

[Gasman]

Is this not because PlateNo can be null?
You look for it as a zero, in FindFirst, yet say it is a text type?

So use the NZ function in your Dlookup?

However I'd sort out what it is meant to be really?

 

[mba_110]

I did joint the two table to query but unable to filter records from cboRecordID

on form i have two combo box one is cboPlateNo and another is cboRecordID

Vehicle plate no is unbound cboPlateNo
Insurance transactions is cboRecordID whatever the insurance renewals for plate no which is (cboPlateNo) will be listed in cboRecordID i have this setup, but the issue is After update event of cboRecordID which is giving "syntax error in string expression"

My Afterupdate event in cboRecordID is below.

Me.Recordset.FindFirst "VehicleInsuranceID = '" & Nz(Me.cboPlateNo, 0)

I want when i select the cboPlateNo for any plate no then cboRecordID should show transactions related to that Plate no and when i select any of those transactions in cboRecordID then fields on form should change to that record and should be editable.

I have attached the strip version of my database to your review.

 

[JHB]

Change it to:

Me.Recordset.FindFirst "VehicleInsuranceID = " & Me.cboRecordID

And then unlock all the controls that you want editable.
Here is an example how to unlock the color control.

Me.txtColor.Locked = False