using combo boxes

  • Thread starter Thread starter Sam Carleton
  • Start date Start date
S

Sam Carleton

Ok, I have been a C/C++ windows developer for many years, but
never had to do much with Access, until now. I want to create a
form based on one query, one of the fields is the CustomerID,
rather then showing me the CustomerID, I want to use a combo box
to display the name. I also want to be able to select a
different name and have that person's order show up.

I am sure there is a way to do this in Access, I simply cannot
figure out how. Can someone enlighten me? Ideally I would like
to find a HOWTO web site that will walk me through doing something
like this.

Sam
 
You can have more than one field in a combo box in Access, and you don't
have to display all of the fields.

A typical approach would be to have your query return CustomerID and
CustomerName. Use that query as the RowSource for the combo box (with the
RowSourceType set to Table/Query). Set the ColumnCount to 2, and the
ColumnWidths to 0 (or 0;2, or something like that). Set the BoundColumn to
1.

Now, the combo box will only show the CustomerName, but when you refer to
the control in your code, it will return the CustomerId, which you can then
use to do your lookup for the order information.
 
Hi,
The typical way to do this is to have a query that pulls the CustomerID
and the full name (which you probably will have to concatenate).
So something like:

Select CustomerID, lName & ", " & fName As FullName From someTable
Order By lName

So this is the row source for the combo.
On the properties sheet, make it has two columns and make
the width of the 1st column 0 (so that you do not see the Id field).
Also, make the 1st column the bound column.

Okay, now the record source for the form that your combo is on should
pull all the orders for all customers.

In the AfterUpdate event of your combo you put:

Me.RecordsetClone.FindFirst "[CustomerId] = " & Me.Combo4
Me.Bookmark = Me.RecordsetClone.Bookmark

Substitute the correct names for your combo

You can also use the wizard to add a combo to a bound form and it will do all that for you.
 
Hi, The typical way to do this is to have a query that pulls the
CustomerID and the full name (which you probably will have to
concatenate). So something like:

Select CustomerID, lName & ", " & fName As FullName From
someTable Order By lName

So this is the row source for the combo. On the properties
sheet, make it has two columns and make the width of the 1st
column 0 (so that you do not see the Id field). Also, make the
1st column the bound column.

Okay, now the record source for the form that your combo is on
should pull all the orders for all customers.

In the AfterUpdate event of your combo you put:

Me.RecordsetClone.FindFirst "[CustomerId] = " & Me.Combo4
Me.Bookmark = Me.RecordsetClone.Bookmark

Substitute the correct names for your combo

You can also use the wizard to add a combo to a bound form and
it will do all that for you.
Click to expand...

Hum, I follow exactly what you are doing, I even found a sample
doing the same basic thing. The problem is that my recordset does
not ahve a FindFirst method on it. This is what I have:

Private Sub cboFullnames_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = str(Me.cboFullnames)
rst.FindFirst "Parishioners.ID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

When I go to run it, it clames there is no FindFirst method on
RecordSet. Any thoughts on what is going on? I am running Access
XP SP3 w/ VB 6.3

Sam
 
Hi,
Try declaring your variable as:
DAO.Recordset

Also, make sure you have a reference set to the DAO library.
It must be returning you an ADO recordset because they don't have a
FindFirst method, only a Find.

--
HTH
Dan Artuso, Access MVP


Nony Buz said:
Hi, The typical way to do this is to have a query that pulls the
CustomerID and the full name (which you probably will have to
concatenate). So something like:

Select CustomerID, lName & ", " & fName As FullName From
someTable Order By lName

So this is the row source for the combo. On the properties
sheet, make it has two columns and make the width of the 1st
column 0 (so that you do not see the Id field). Also, make the
1st column the bound column.

Okay, now the record source for the form that your combo is on
should pull all the orders for all customers.

In the AfterUpdate event of your combo you put:

Me.RecordsetClone.FindFirst "[CustomerId] = " & Me.Combo4
Me.Bookmark = Me.RecordsetClone.Bookmark

Substitute the correct names for your combo

You can also use the wizard to add a combo to a bound form and
it will do all that for you.
Click to expand...

Hum, I follow exactly what you are doing, I even found a sample
doing the same basic thing. The problem is that my recordset does
not ahve a FindFirst method on it. This is what I have:

Private Sub cboFullnames_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = str(Me.cboFullnames)
rst.FindFirst "Parishioners.ID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

When I go to run it, it clames there is no FindFirst method on
RecordSet. Any thoughts on what is going on? I am running Access
XP SP3 w/ VB 6.3

Sam
Click to expand...
 
Back
Top