Combo Box To Go To Existing Record or Go To New

[Jules-H]

Hi

I'm new to access and have been tasked to create a database for my work!!

I have two table, one for customers and one with appointments. The customer
has a unique ID generated by another system and this and their address info
is entered into the Customer form. I am using a subform to show linked
appointments.

I am looking for a way for users to enter the customer ID (in a combo box
maybe?) at the top of the form and if that ID exists for it to pull the
customer info and appointments up. If an unknown customer ID is entered I
would like for a blank form to be pulled so that data can be entered. I've
been trying to do this for days now and can't find any way. Does anyone know
if it's possible.

I'd be really grateful for any advice.

Thanks.

 

[golfinray]

You have to use some code to do that. If you right click on the combo box and
go to the afterupdate event then click on the code builder and type:
Me.filter = "[ID] = """ & me.combo# & """"
Me.filteron = true
The combo # will be listed, like combo12 or combo20

 

[BruceM]

The combo box wizard will let you set up a combo box to find the record
indicated by the combo box selection. Without using the wizard you can
create an unbound combo box. Its Row Source is a query based on the
Customer table. Add the ID field and the customer name. Combo box Column
Count is 2, Column Widths are something like 0";1.5", and the Bound Column
is 1. In the combo box After Update event:

Private Sub cboFindCustomer_AfterUpdate()

' Find the record that matches the combo box selection

On Error GoTo ProcErr

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboFindCustomer
Me.Bookmark = rs.Bookmark

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in cboFindCustomer_AfterUpdate, Form_frmCustomer"
Resume ProcExit

End Sub

The code assumes CustomerID is the name of the ID field, cboFindCustomer is
the name of the combo box, and frmCustomer is the form name.

You can use the combo box Not In List event to add a new customer if the
customer is not listed, but it may be simpler just to use a New Record
button if the customer does not appear in the list. I will leave out the
details for now.

If you would like to do this as you stated, using the number in a text box,
that can be done, but the syntax is a little different if you want to add a
new record if there is no matching number. Consider, though, that there is
a lot of potential for "false negatives" if done this way. If somebody
cannot recall the number, or thinks they remember it but do not, you could
end up at a new record when there is already a record for that customer.
The combo box show you the actual customer name, which has far less chance
for error, and spares the user from having to look up or remember the ID.

 

[Jules-H via AccessMonster.com]

Hi

Thanks for your help. I entered the code in the After Update event as you
described.

I'm now getting a message saying 'The record cannot be deleted or changed
because the table 'tblAppts ' includes related records.

Sorry to be a pain. I've been working on this trying to find a solution for
2 days now. I had no idea it could be so complicated.

The combo box wizard will let you set up a combo box to find the record
indicated by the combo box selection. Without using the wizard you can
create an unbound combo box. Its Row Source is a query based on the
Customer table. Add the ID field and the customer name. Combo box Column
Count is 2, Column Widths are something like 0";1.5", and the Bound Column
is 1. In the combo box After Update event:

Private Sub cboFindCustomer_AfterUpdate()

' Find the record that matches the combo box selection

On Error GoTo ProcErr

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboFindCustomer
Me.Bookmark = rs.Bookmark

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in cboFindCustomer_AfterUpdate, Form_frmCustomer"
Resume ProcExit

End Sub

The code assumes CustomerID is the name of the ID field, cboFindCustomer is
the name of the combo box, and frmCustomer is the form name.

You can use the combo box Not In List event to add a new customer if the
customer is not listed, but it may be simpler just to use a New Record
button if the customer does not appear in the list. I will leave out the
details for now.

If you would like to do this as you stated, using the number in a text box,
that can be done, but the syntax is a little different if you want to add a
new record if there is no matching number. Consider, though, that there is
a lot of potential for "false negatives" if done this way. If somebody
cannot recall the number, or thinks they remember it but do not, you could
end up at a new record when there is already a record for that customer.
The combo box show you the actual customer name, which has far less chance
for error, and spares the user from having to look up or remember the ID.

Hi

[quoted text clipped - 19 lines]

Thanks.

 

[BruceM]

The code I posted does not delete a record, so you should not be getting
that message. Please post the exact After Update code you are using.

Hi

Thanks for your help. I entered the code in the After Update event as you
described.

I'm now getting a message saying 'The record cannot be deleted or changed
because the table 'tblAppts ' includes related records.

Sorry to be a pain. I've been working on this trying to find a solution
for
2 days now. I had no idea it could be so complicated.

The combo box wizard will let you set up a combo box to find the record
indicated by the combo box selection. Without using the wizard you can
create an unbound combo box. Its Row Source is a query based on the
Customer table. Add the ID field and the customer name. Combo box Column
Count is 2, Column Widths are something like 0";1.5", and the Bound Column
is 1. In the combo box After Update event:

Private Sub cboFindCustomer_AfterUpdate()

' Find the record that matches the combo box selection

On Error GoTo ProcErr

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[CustomerID] = " & Me.cboFindCustomer
Me.Bookmark = rs.Bookmark

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in cboFindCustomer_AfterUpdate, Form_frmCustomer"
Resume ProcExit

End Sub

The code assumes CustomerID is the name of the ID field, cboFindCustomer
is
the name of the combo box, and frmCustomer is the form name.

You can use the combo box Not In List event to add a new customer if the
customer is not listed, but it may be simpler just to use a New Record
button if the customer does not appear in the list. I will leave out the
details for now.

If you would like to do this as you stated, using the number in a text
box,
that can be done, but the syntax is a little different if you want to add
a
new record if there is no matching number. Consider, though, that there
is
a lot of potential for "false negatives" if done this way. If somebody
cannot recall the number, or thinks they remember it but do not, you could
end up at a new record when there is already a record for that customer.
The combo box show you the actual customer name, which has far less chance
for error, and spares the user from having to look up or remember the ID.

Hi

[quoted text clipped - 19 lines]

Thanks.

 

[Jules-H via AccessMonster.com]

Private Sub cmbFindRTH_AfterUpdate()

' Find the record that matches the combo box selection

On Error GoTo ProcErr

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[RTH No:] = " & Me.cmbFindRTH
Me.Bookmark = rs.Bookmark

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in cmbFindRTH_AfterUpdate, Form_frmPatient"
Resume ProcExit

End Sub


Is the code I typed in. I created the unbound combo box and added the data.
It isn't coming up with an error anymore since I deleted the original combo
and created yours from scratch. But now I can drop down and select the ID
number and the corresponding data is displayed but if I type a new number in
to the combo box it returns data on a non-corresponding ID??

You must be a super genius, I feel like I'm punching way beyond my weight!
Is there a chance I can learn this stuff before I retire?

 

[BruceM]

No genius, just determined and methodical. In fact, most of what I learned
was through these newsgroups and the links I found here and on my own.

If a user types a number that does not exist, Access goes to the first
record, I think.

If you click the drop-down arrow on the combo box, are you looking at a list
of numbers? If so, wouldn't you rather look at a list of names? What you
seem to be asking is that if somebody types in a customer ID that is not in
the database, you want to add a new customer record with that ID. If so,
sooner or later a user will decide it is easier to add a new customer record
than to dig out the list of customers to find the ID number. If not that
exactly, it will almost surely create problems.

Consider using a query consisting of CustomerID and CustomerName as the
combo box RowSource. Set the Bound Column for the combo box to 1, the
Column Count to 2, and the Column widths to 0",1.5" (or whatever for the
second number). This way the user will be seeing a list of names, not
numbers.

In any case, set the Not In List property for the combo box to Yes. For the
Not In List event, the simplest is something like:

MsgBox "No such vendor. Click the New Record button."
Me.cmbRindRTH.Undo
Me.SomeControl.SetFocus
Response = acDataErrContinue

The SetFocus line moves the focus to another control (your choice), and
thereby closes the drop-down list, which would otherwise remain open.

Bottom line is that the user sees the customer is not on the list, and
either goes to the new record button without prompting, or types in a
non-existent user and thus invokes the NotInList code.

I should have checked more carefully about using the Not In List directly to
add a new record. In my experiments I could not get it to work simply, but
I expect it is possible (although it may require quite a bit of coding).
Frankly, I have only used Not In List to add a new record via a bound combo
box (for adding a new product in a Purchase Order form, for instance).

Private Sub cmbFindRTH_AfterUpdate()

' Find the record that matches the combo box selection

On Error GoTo ProcErr

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[RTH No:] = " & Me.cmbFindRTH
Me.Bookmark = rs.Bookmark

ProcExit:
Exit Sub

ProcErr:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in cmbFindRTH_AfterUpdate, Form_frmPatient"
Resume ProcExit

End Sub


Is the code I typed in. I created the unbound combo box and added the
data.
It isn't coming up with an error anymore since I deleted the original
combo
and created yours from scratch. But now I can drop down and select the ID
number and the corresponding data is displayed but if I type a new number
in
to the combo box it returns data on a non-corresponding ID??

You must be a super genius, I feel like I'm punching way beyond my weight!
Is there a chance I can learn this stuff before I retire?