How to use a Combo box for searching and data entry (1 Viewer)

wildcal

New member
Local time
Yesterday, 21:47
Joined
Jan 25, 2017
Messages
3
I have a form with a Combo box that is set up as a lookup (not sure if this is the correct terminology). When the user selects a Customer Name from the Combo Box picklist, the fields for the customer address are automatically populated with the address info from the related record in a table. So basically this allows me to search for a customer and then view the customer's address info. But I want to also be able to add a new Customer and their address using this same form. To do this, I created a Add New Site command button that uses the following command to bring up an new (ie empty) record: DoCmd.GoToRecord, , acNewRec
The problem is, when I click the Add New Site button, a blank record is brought up for all the fields except the Combo Box. It keeps the last selected value showing. How do I get it to clear and allow me to enter a new Customer Name? And since the Customer Name combo box is not bound (because it gets its source from the Row Source Type and Row Source rather than a Control Source), how would the new name get saved to the table.
Note: My form also has a SAVE button so that edits aren't saved until you hit the SAVE button.
Surely it's fairly common to use a combo box or list box for navigating to a particular record and also for entering new records.
I'm pretty new to setting up forms and using VBA, so please forgive me if I'm not explaining well.
Thanks,
wildcal
 

Ranman256

Well-known member
Local time
Today, 00:47
Joined
Apr 9, 2015
Messages
4,339
You filter the form....
Code:
Sub cboBox_afterupdate()
If IsNull(cboBox) then 
   Me.filterOn= false
Else
   Me.filter= "[field]='" & me.cboBox & "'"
   Me.filterOn= true
End if
End sub

Put a BOUND combBox on the form for the record value.
The unbound one is for filtering.
 

wildcal

New member
Local time
Yesterday, 21:47
Joined
Jan 25, 2017
Messages
3
So I have to have two controls for the Customer Name? There's no way to do it with a single combo box or maybe a list box?
 

MarkK

bit cruncher
Local time
Yesterday, 21:47
Joined
Mar 17, 2004
Messages
8,179
I wouldn't use the same control for searches in one mode, and for editing data in a different mode. This invites somebody mistaking the mode and commanding the wrong action, potentially wiping out the data in the field.
 

Ranman256

Well-known member
Local time
Today, 00:47
Joined
Apr 9, 2015
Messages
4,339
no, you cant search and store data in one control.
 

wildcal

New member
Local time
Yesterday, 21:47
Joined
Jan 25, 2017
Messages
3
Thanks to all the responders for the feedback. I appreciate the guidance.

wildcal
 

Users who are viewing this thread

Top Bottom