Combo Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box on a form that is based on a concatenation of two fields
(Firstname & "" &LastName. Access identifies field this as a query). My
problem is that I would like to populate the remaining fields on this form
based on my selection in the combo box. My problem is that I do not know how
to reference a query in Visual Basic (AfterUpdateEvent). What syntax do I
need to use, in order to reference the query field in Visual Basic.
 
Olu said:
I have a combo box on a form that is based on a concatenation of two
fields (Firstname & "" &LastName. Access identifies field this as a
query). My problem is that I would like to populate the remaining
fields on this form based on my selection in the combo box. My
problem is that I do not know how to reference a query in Visual
Basic (AfterUpdateEvent). What syntax do I need to use, in order to
reference the query field in Visual Basic.
Click to expand...

Easiest way to do this is to add the additional data as hidden columns to
your ComboBox. Then in your AfterUpdate event you can simply grab the data
from there.

Me!SomeField = Me!ComboBoxName.Column(1)
Me!SomeOtherField = Me!ComboBoxName.Column(2)
etc..

Of course the first thing you should realize is that copying multiple fields
from one table into another *usually* suggests that your design is
incorrect. Normally you only copy the key field(s) and then use lookup
tools to *display* the other related data.

Two exceptions to this rule would be...

You only want the related data as default values in the second table and
want to allow the user to override it on occasion.

The data is time senstive and you need the second table to capture the data
as it is now knowing that the data in the first table will change later
(like prices for example).
 
Olu,

Be careful to distinguish between fields, which exist in tables, and
controls, which exist on forms. The latter may be Bound to table in the
underlying RecordSource, be calculated from an expression, or may be Unbound.


The distinction is important because a combo box normally *displays*
meaningful text such as the concatenated name in your case, but *stores* the
primary key value associated with this name (a foreign key in the table
underlying your form). This is done by including the primary key field in
the RowSource of the combo box, setting the Bound Column to 1, and the first
ColumnWidth to 0".

The foreign key should be the only field you store in your form's underlying
table. It fully identifies the record. You may *display* other fields from
the RowSource table either by including them in the RowSource (normally
setting their ColumnWidths to 0"), and using the Column property of the combo
box, or basing your form on a query that links the two tables by the primary
and foreign key, and includes the other fields you wish to display.

The Column property index starts with 0, so to display the third column in a
textbox, set its ControlSource to:

=YourComboBox.Column(2)

Hope that helps.
Sprinks
 
I think you would like the afterupdate event of the combo to be something
like this
Sub Mycombo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Mycombo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[YourNameField] = """ & Me.cbomoveto & """"
rs.FindFirst "[YourNameFieldID] = " & Me.cbomoveto & "" (this line if a
numberic field)
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
You really should base your combo on a primary ID field.
 
I forgot to mention that I also have a subform within the form.
I am using the Combobox has my lookup field. Is this right?
I had previously used the EmployeeID field has my lookup field in the
Combobox, but when I select it, i get the error message that I cannot
duplicate values - INDEX KEY., but the funny thing is that the data on the
form seems to synchronize only when I click on the navigation button on my
form, but not when I select it from the combo box. That is why I decided to
use the concatenation field in the ComboBox.

Does this make sense?
 
I had previously used the EmployeeID field has my lookup field in the
Combobox, but when I attempt to make my selection, i get the error message
that I cannot
duplicate values - INDEX KEY., but the funny thing is that the forms
synchronize only when I use the navigation button on my
form, but not when I make my selection from the combo box. That is why I
decided to
use the concatenation field in the ComboBox. I will go back to using the
EmployeeID key which is a primary ID field, and see if it works.

Thanks! Stay tuned:)

Anne said:
I think you would like the afterupdate event of the combo to be something
like this
Sub Mycombo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Mycombo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[YourNameField] = """ & Me.cbomoveto & """"
rs.FindFirst "[YourNameFieldID] = " & Me.cbomoveto & "" (this line if a
numberic field)
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
You really should base your combo on a primary ID field.

Olu Solaru said:
I have a combo box on a form that is based on a concatenation of two fields
(Firstname & "" &LastName. Access identifies field this as a query). My
problem is that I would like to populate the remaining fields on this form
based on my selection in the combo box. My problem is that I do not know how
to reference a query in Visual Basic (AfterUpdateEvent). What syntax do I
need to use, in order to reference the query field in Visual Basic.
Click to expand...
Click to expand...
 
I tried your suggestion but I get the following error message:
Compile error: Method or data member not found.

and highligts the cbomoveto in the following line: rs.FindFirst
"[YourNameField] = """ & Me.cbomoveto & """"

Note - EmployeeID is a text field.

Anne said:
I think you would like the afterupdate event of the combo to be something
like this
Sub Mycombo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Mycombo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[YourNameField] = """ & Me.cbomoveto & """"
rs.FindFirst "[YourNameFieldID] = " & Me.cbomoveto & "" (this line if a
numberic field)
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
You really should base your combo on a primary ID field.

Olu Solaru said:
I have a combo box on a form that is based on a concatenation of two fields
(Firstname & "" &LastName. Access identifies field this as a query). My
problem is that I would like to populate the remaining fields on this form
based on my selection in the combo box. My problem is that I do not know how
to reference a query in Visual Basic (AfterUpdateEvent). What syntax do I
need to use, in order to reference the query field in Visual Basic.
Click to expand...
Click to expand...
 
I messed up,, the name of your combobox should be the same throughout.
if your combo is called mycombo the "Me.cboMoveto" should be "Me.MyCombo"

You combo should be unbound and the form should be based on a query or table
which lists that field on the form.

Please look at this link on Allen Browne's website, this is where I got this
from:
http://allenbrowne.com/ser-03.html
Anne



Olu Solaru said:
I tried your suggestion but I get the following error message:
Compile error: Method or data member not found.

and highligts the cbomoveto in the following line: rs.FindFirst
"[YourNameField] = """ & Me.cbomoveto & """"

Note - EmployeeID is a text field.

Anne said:
I think you would like the afterupdate event of the combo to be something
like this
Sub Mycombo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Mycombo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[YourNameField] = """ & Me.Mycombo & """"
If rs.NoMatch Then
Click to expand...
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub
You really should base your combo on a primary ID field.

Olu Solaru said:
I have a combo box on a form that is based on a concatenation of two fields
(Firstname & "" &LastName. Access identifies field this as a query). My
problem is that I would like to populate the remaining fields on this form
based on my selection in the combo box. My problem is that I do not know how
to reference a query in Visual Basic (AfterUpdateEvent). What syntax do I
need to use, in order to reference the query field in Visual Basic.
Click to expand...
Click to expand...
Click to expand...
 
Olu said:
I forgot to mention that I also have a subform within the form.
I am using the Combobox has my lookup field. Is this right?
I had previously used the EmployeeID field has my lookup field in the
Combobox, but when I select it, i get the error message that I cannot
duplicate values - INDEX KEY., but the funny thing is that the data
on the form seems to synchronize only when I click on the navigation
button on my form, but not when I select it from the combo box. That
is why I decided to use the concatenation field in the ComboBox.

Does this make sense?
Click to expand...

If you are using a ComboBox as a means to move to a different record then it
should not be bound to a field in your table. A ComboBox is either used to
navigate (unbound) OR as a means to enter data (bound). You can't do both at
the same time.
 
Back
Top