Open Form to a Combo Box on another Form

[MHBear21]

I am creating a dashboard and would like to put an event in the "On Dbl Click" that will open up the details to a second form. The second form is unbound with a combo box used as a filter to a subform.

The first form is subfrmInvoiceDB with the fields built from a query:
Contract_Number- Text
Invoice_No- Text
Invoice_Date- Date

Form to open up to is frmInvoices (Unbound):
cboContractNo (Contract_Number Column 1 and Contract_Data_ID column 2) bound column is 2 with the following code:

Private Sub cboJAANo_AfterUpdate()
Dim sql As String, Fieldname As String, FieldValue As String
Fieldname = "Contract_Number="
FieldValue = cboContractNo.Value
sql = Fieldname & Chr(34) & FieldValue & Chr(34)

Forms!frmInvoices.Filter = sql
Forms!frmInvoices.FilterOn = False

End Sub

I have tried modifing the code at baldyweb but am not having any luck. I also looked at code for the OpenArgs with no luck. Am new to VBA and think I missing something easy.

Dim rs As Object
Dim lngBookmark As Long

'set a variable to the current record
lngBookmark = Me.txtEmpID
'open the new form
DoCmd.OpenForm "frmEmployeesDetail"

'take it to the selected record
Set rs = Forms!frmEmployeesDetail.RecordsetClone
rs.FindFirst "EmpID = " & lngBookmark
Forms!frmEmployeesDetail.Bookmark = rs.Bookmark

Set rs = Nothing

Thanks, Anything would be helpful. Using Access 2010

 

[GinaWhipp]

I think you are looking for...

If field is numeric…

DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]=" & Me![NameOfFieldOnFormOpeningFrom]

If field is text…

DoCmd.OpenForm "YourFormName", , , "[NameOfFieldFromDataSource]='" & Me![NameOfFieldOnFormOpeningFrom] & "'"

 

[MHBear21]

Here is the code I put in, but I am getting a Enter Parameter Value on cboContractNo, but when I enter the Contract Number it opens the form blank.

When I select a record from the combo box the error "Run-time error '37093: The search key was not found in any record. I can hit the end and it will populate.

Private Sub Contract_Number_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmInvoices", , , "[cboContractNo]='" & Me![Contract_Number] & "'"
End Sub

Any suggestions?

 

[GinaWhipp]

What are the columns in the Combo Box? Is the first one the Contract Number?

 

[MHBear21]

The combo box is as follows:
cboContractNo
Contract_Number Column 1 - Text field (Only visible column)
Contract_Data_ID Column 2 - AutoNumber (Bound Column Hidden)

 

[GinaWhipp]

Well then you need to link it to the *Contract Data ID* column because it is the Bound column. You can link them with one being the ID and the other being the actual number. You need to also bear in mind it's Text to Text or Number to Number and not a combn=ination of the two.

 

[MHBear21]

Gina, thanks for the help. I have tried the below, Number to Number, and am still getting the same error Enter the Parameter value on cboContractNo then nothing comes up.

DoCmd.OpenForm "frmInvoices", , , "[cboContractNo]=" & Me![Contract_Data_ID]

If I try and select a value in the combo I get error 3709. When I click debug it takes me to the bolded code below in the AfterUpdate of cboContractNo.

Private Sub cboContractNo_AfterUpdate()
Dim sql As String, Fieldname As String, FieldValue As String
Fieldname = "Contract_Number="
FieldValue = cboContractNo.Value
sql = Fieldname & Chr(34) & FieldValue & Chr(34)

Forms!frmInvoices.Filter = sql
Forms!frmInvoices.FilterOn = False
End Sub

Is there code somewhere that I am missing?

 

[GinaWhipp]

Did you change the Comob Boxes BOUND column to the the column with the number?

 

[MHBear21]

No, I have not changed the bound column. The bound column is still the Contract_Data_ID. It is the link to a subform on the frmInvoices so I don't want to change it.

 

[GinaWhipp]

Does this field Contract_Number match cbContractNo? I think not based on your previous postings. So, something has to change... Either cbContractNo needs to be the same (BOUND column) as Contract_Number OR you have to use the Contract_Data_ID as the *link* to cboContractNo.

You can't mix and match this or it will never work.

 

[MHBear21]

Gina, sorry I'm not sure I understand where I am not putting in the right fields.

I have tried this code in both the Contract_Number and Contract_Data_ Id fields with the same error.

DoCmd.OpenForm "frmInvoices", , , "[cboContractNo]=" & Me![Contract_Data_ID]

 

[GinaWhipp]

Are both fields the same Data Type? Number to number OR text to text...