Passing Criteria between forms issue 2

[Bronte1226]

Hi there. I have an issue with getting my form to open with the correct information after it the record is chosen on another form. The second form opens but with no record selected. When I check the source of the opened form, it seems to be picking the wrong data. (i.e. instead of name, it using a number as a criterion) However, when I try to change it, the whole thing blows up.

Here is my code thus far on the originating form:

Option Compare Database
Option Explicit

Private Sub cboCategories_AfterUpdate()
On Error GoTo ErrorHandler

Dim strSQL As String

Me.cboProducts_Query.Requery

Cleanup:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Cleanup

End Sub


Private Sub cboProducts_Query_DblClick(Cancel As Integer)
On Error GoTo Err_cboProducts_Query_DblClick

Dim stDocName As String
Dim stLinkCriteria As String


stDocName = "frm_vendor_qrycat"

stLinkCriteria = "[vendor name]= '" & Me![cboProducts_Query] & "'"
DoCmd.Close
DoCmd.OpenForm stDocName, , , stLinkCriteria


Exit_cboProducts_Query_DblClick:
Exit Sub

Err_cboProducts_Query_DblClick:
MsgBox Err.Description
Resume Exit_cboProducts_Query_DblClick

End Sub

Private Sub cmdExit_Click()
On Error GoTo ErrorHandler

DoCmd.Close acForm, Me.Name

Cleanup:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Cleanup

End Sub

Private Sub Form_Load()
On Error GoTo ErrorHandler

Dim strSQL As String


strSQL = "SELECT CatID, [Vendor Name]" & _
"FROM qry_VendorSearchByCategory " & _
"ORDER BY [Vendor Name];"

Me.cboProducts_SQL_Nulls.RowSource = strSQL

Cleanup:
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Cleanup

End Sub

 

[MajP]

Me![cboProducts_Query]

This combo is likely bound to a hidden column. My guess it is the Product id.

check
column count:
column widths:
bound column:

my guess
2
0;somevalue"
1

or you are simply referencing the wrong combo
your names suggest Vendor and Product which may be wrong.
"[vendor name]= '" & Me![cboProducts_Query] & "'

 

[dhookom]

I expect your combo box is bound to a column that you might not be seeing. It would help if you would have provided some of the significant properties of cboProduct_Query.

The solution could be as simple as referencing a column of the combo box. Columns are numbered beginning with 0.

Private Sub cboProducts_Query_DblClick(Cancel As Integer)
  On Error GoTo Err_cboProducts_Query_DblClick
  Dim stDocName As String
  Dim stLinkCriteria As String
  stDocName = "frm_vendor_qrycat"
  stLinkCriteria = "[vendor name]= '" & Me![cboProducts_Query].column(1) & "'"
  DoCmd.Close
  DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cboProducts_Query_DblClick:
  Exit Sub

Err_cboProducts_Query_DblClick:
  MsgBox Err.Description
  Resume Exit_cboProducts_Query_DblClick
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Bronte1226]

dhookom, That was it! It was bound to the wrong column! Switched that, and it worked! Many thanks!!

 

[Bronte1226]

That was it guys!

The cbo was bound to the wrong column in the query. Switched it to the correct field and voila, success!

Many many thanks!!

This place is awesome with helpful souls like you both!