Combo Box Query

[Guest]

Hi
I have a form which its record source is a qry, however if the data is not
complete ie missing an ABN number then another form is opened and is meant to
show the details off the Supplier in the main table which is a part of the
qry.

What I am having difficulty with is getting the combo box on the second for
(supplier details) to show the record of the supplier.
It works if I select the supplier from the combo box but I want it to also
work if the supplier name is populated using code from the first form.

Any idea's how I can do this. Below is the code I currently have:

Form 1 code (which enters the name into the combo box)

If blnOPV = False Then
DoCmd.Close acForm, "frm_SplashBack"
DoCmd.OpenForm "frm_Find"
Else
DoCmd.OpenForm "frm_SupplierDetails"
Form_frm_SupplierDetails.cboName.Value = stName
End If

Form 2 code (which should populate all the other fields with the suppliers
details which has just been entered into the combo box but doesn't)

Public Sub cboName_AfterUpdate()
'Purpose: search for the supplier using the combo box and populate the
fields
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SupplierName] = '" & Me![cboName] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Any idea's how I can get my code to work

Thanks
Noemi

 

[Guest]

Hi Noemi,

Why not change the code to open the form with the specified criteria and
then let the combo look at the field. Something like:

Form 1 code (which enters the name into the combo box)

If blnOPV = False Then
DoCmd.Close acForm, "frm_SplashBack"
DoCmd.OpenForm "frm_Find"
Else
DoCmd.OpenForm "frm_SupplierDetails",,"[SupplierName]='" &
me.[SupplierName] & "'"
Form_frm_SupplierDetails.cboName=
Form_frm_SupplierDetails.SupplierName
End If

hth

 

[Guest]

Hi Maurice
I entered your code and it comes up with the following error

Microsoft access can't find the field 'l' referred to in your expression.

I have no idea what the error means.

Any ideas

Thanks Noemi

Hi Noemi,

Why not change the code to open the form with the specified criteria and
then let the combo look at the field. Something like:

Form 1 code (which enters the name into the combo box)

If blnOPV = False Then
DoCmd.Close acForm, "frm_SplashBack"
DoCmd.OpenForm "frm_Find"
Else
DoCmd.OpenForm "frm_SupplierDetails",,"[SupplierName]='" &
me.[SupplierName] & "'"
Form_frm_SupplierDetails.cboName=
Form_frm_SupplierDetails.SupplierName
End If

hth

--
Maurice Ausum

Hi
I have a form which its record source is a qry, however if the data is not
complete ie missing an ABN number then another form is opened and is meant to
show the details off the Supplier in the main table which is a part of the
qry.

What I am having difficulty with is getting the combo box on the second for
(supplier details) to show the record of the supplier.
It works if I select the supplier from the combo box but I want it to also
work if the supplier name is populated using code from the first form.

Any idea's how I can do this. Below is the code I currently have:

Form 1 code (which enters the name into the combo box)

If blnOPV = False Then
DoCmd.Close acForm, "frm_SplashBack"
DoCmd.OpenForm "frm_Find"
Else
DoCmd.OpenForm "frm_SupplierDetails"
Form_frm_SupplierDetails.cboName.Value = stName
End If

Form 2 code (which should populate all the other fields with the suppliers
details which has just been entered into the combo box but doesn't)

Public Sub cboName_AfterUpdate()
'Purpose: search for the supplier using the combo box and populate the
fields
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SupplierName] = '" & Me![cboName] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Any idea's how I can get my code to work

Thanks
Noemi

 

[Guest]

I fixed the error I was getting however I still dont get the detaisl to
populate for that record into the form, all that happens is the first record
is shown

Hi Noemi,

Why not change the code to open the form with the specified criteria and
then let the combo look at the field. Something like:

Form 1 code (which enters the name into the combo box)

If blnOPV = False Then
DoCmd.Close acForm, "frm_SplashBack"
DoCmd.OpenForm "frm_Find"
Else
DoCmd.OpenForm "frm_SupplierDetails",,"[SupplierName]='" &
me.[SupplierName] & "'"
Form_frm_SupplierDetails.cboName=
Form_frm_SupplierDetails.SupplierName
End If

hth

--
Maurice Ausum

Hi
I have a form which its record source is a qry, however if the data is not
complete ie missing an ABN number then another form is opened and is meant to
show the details off the Supplier in the main table which is a part of the
qry.

What I am having difficulty with is getting the combo box on the second for
(supplier details) to show the record of the supplier.
It works if I select the supplier from the combo box but I want it to also
work if the supplier name is populated using code from the first form.

Any idea's how I can do this. Below is the code I currently have:

Form 1 code (which enters the name into the combo box)

If blnOPV = False Then
DoCmd.Close acForm, "frm_SplashBack"
DoCmd.OpenForm "frm_Find"
Else
DoCmd.OpenForm "frm_SupplierDetails"
Form_frm_SupplierDetails.cboName.Value = stName
End If

Form 2 code (which should populate all the other fields with the suppliers
details which has just been entered into the combo box but doesn't)

Public Sub cboName_AfterUpdate()
'Purpose: search for the supplier using the combo box and populate the
fields
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[SupplierName] = '" & Me![cboName] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Any idea's how I can get my code to work

Thanks
Noemi