FindFirst Criteria

[caferacer]

Hi,

Anyone got any ideas why Access says the engine doesn't recognise JunctionID as a valid field name or expression? Have tried numerous ways to re configure the srtCriteria line with no success?

Thanks


Dim ProductID As Integer
Dim JunctionID As Integer
Dim strCriteria As String

ProductID = Me.cboFindProduct.Column(0)
JunctionID = Me.cboFindProduct.Column(1)
strCriteria = "[ProductID] = " & ProductID & " And [JunctionID] = " & JunctionID

 

[Galaxiom]

Put a Break Point on the last line and look at the values of the variables to see if they are what you expect.

 

[caferacer]

Not a VBA expert, but I did have something similar in just below strCriteria =....

MsgBox "Junction ID = " & JunctionID & " Product ID = " & ProductID & ""

The values are what I expected.

The only other code in the Combo After update event apart from error handling is

Set rs = Me.RecordsetClone
rs.FindFirst strCriteria

Thanks for helping.

Regards

Mark

 

[caferacer]

This is the SQL from the combo if it helps diagnose the issue?

SELECT tblProduct.ProductID, tblProductSupplier.JunctionID, tblProduct.Description, tblSupplier.[Supplier Name], tblProductSupplier.SupplierIDFK
FROM tblSupplier RIGHT JOIN (tblProduct LEFT JOIN tblProductSupplier ON tblProduct.ProductID = tblProductSupplier.ProductIDFK) ON tblSupplier.SupplierID = tblProductSupplier.SupplierIDFK;

Thanks.

 

[caferacer]

Just dawned on me, the field JunctionID is on a subform so the syntax pointing to the field in the FindFirst could also be a potential problem.

The subform is [frmPoductSuppliers], the frame of the subform is also [frmPoductSuppliers]. However I have still not had any success. I have tried

strCriteria = "[ProductID] = " & ProductID & " And Me.frmProductSuppliers.form.[JunctionID] = " & JunctionID

Any thoughts?

Thanks

 

[CJ_London]

would have thought you wanted

strCriteria = "[ProductID] = " & ProductID & " And JunctionID=" & Me.frmProductSuppliers.form.[JunctionID]

 

[caferacer]

Still says MS Engine does not recognise JunctionID as a valid field name or expression??

Dim rs As DAO.Recordset
Dim ProductID As Integer
Dim JunctionID As Integer
Dim strCriteria As String

ProductID = Me.cboFindProduct.Column(0)
JunctionID = Me.cboFindProduct.Column(1)
strCriteria = "[ProductID] = " & ProductID & " And JunctionID = " & Me.frmProductSuppliers.Form.[JunctionID]
MsgBox "Junction ID = " & JunctionID & " Product ID = " & ProductID & "" 'This line works okay

Set rs = Me.RecordsetClone
rs.FindFirst strCriteria
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If

 

[CJ_London]

you need to check all your names, somewhere you logic is not correct

here you are saying

JunctionID = Me.cboFindProduct.Column(1)

and

MsgBox "Junction ID = " & JunctionID & " Product ID = " & ProductID & "" 'This line works okay

so why are you using

strCriteria = "[ProductID] = " & ProductID & " And JunctionID = " & Me.frmProductSuppliers.Form.[JunctionID]

?

if this works

JunctionID = Me.cboFindProduct.Column(1)

should you be using

Me.frmProductSuppliers.Form.cboFindProduct.Column(1)

finally

Still says MS Engine does not recognise JunctionID as a valid field name or expression??

is junctionID a field in the recordset you are using with the criteria?

 

[spikepl]

Throw out the variable declarations that seemingly have the same names as the fields in the form's recordset and create the strCriteria directly with the text and the corresponding values.

 

[Galaxiom]

If JunctionID is in the subform then it won't be in the Form's recordset (or its clone).

 

[caferacer]

Dear All,

Thanks to everyone for taking the interest and time to help on this.

I am no VBA expert and have just used code from other - what I see as similar situations as a base to start from. Therefore I have no doubt I could well be making fundamental Access mistakes.

I was getting the MSA engine error on JunctionID, so this was the reason for adding the msgbox to see if this also created the error, which with my basic logic thought it should. To my surprise it didn’t and therefore thought I was probably making just a simple syntax error with FindFirst, which was the reason for starting the thread.

What I am trying to achieve is the user needs to choose a Product from a combo box. The combo box lists all Products and Supplier combinations, so you may have in the combo list; ProdA-SuppA, ProdA-SuppB, ProdA-SuppC, ProdB-SuppA, ProdB-SuppB, ProdB-SuppC......,. All combinations are listed in the same combo. Once a selection is made, the main form displays the detail about the Product, with a subform displaying the details of the Supplier.

My quest is if you select ProductA from SupplierB using the combo, I want the active record to move to ProductA (in the main form) & SupplierB (in the subform). At present the best I have achieved is ProductA-SupplierA, with the ability to scroll back and forth in the subform to the correct Supplier, but I want this to automatically be positioned at the correct Supplier straight away, but retain the ability to scroll to the other Suppliers.

Have attached my test DB, which I am using to try and solve the problem.

Thanks all once again.

Regards

Mark

 

[caferacer]

Hi,

After a few more hours, I think I have moved this on (a little).

Have found another example which has helped me modify the code. However, instead of selecting once from the combo, you have to select once for the product and then again for the supplier?? After endless attempts/variations haven’t been able to combine the two selections together, which is the aim.

Is there any way of combining the recordsets or nesting them perhaps....?

The update is attached.

Any ideas?


Thanks

 

[JHB]

Change the below line:

From:       
 rs2.FindFirst "ProductIDFK= " & Me.cboFindProduct.Column(0) & " And JunctionID=" & Me.cboFindProduct.Column(1)
To:
rs2.FindFirst "ProductIDFK= " & Me.cboFindProduct.Column(0) & " And JunctionID=" & JunctionID

 

[Galaxiom]

You would have to find the ProductID in the main form's recordset. Then do the same process with the subform's recordset for the JunctionID.

It seems the message is telling you that JunctionID is not in the main form's recordset.

 

[caferacer]

JHB,

THANK YOU, THANK YOU, THANK YOU, you don't know how much hassle this is going to now save me, with people entering data in the wrong records.

You are most definitely on my favourites list.
Regards

Mark.

 

[JHB]

You're welcome, luck with your project.