Combo Box on form

[eastmanj]

I am not very access literate so please bear with me as I try to explain what I am trying to do. I have a form entitled "mail log" in my database. It used to be set up so that I type in the client's number, it pulls up the client's name, and then I would tab down and fill in the rest of the blocks manually. I want to change it so that there is a combo box in the address block with some of our most used addresses. Then when I select an address, it will put the address in the address block, the city in the city block and the state in the state block; all with one click. I also need the form to then transfer that information to a mail log table. I hope I have been clear enough. Any help would be greatly appreciated.

 

[Ashank4vba]

Hello eastman,

Im afraid I have a rather convulted IDEA which I think will work though.

You could have a query, say addr_qry as follows:

SELECT TOP 5 
COUNT(*) As Occurrence,
mytbl.addr1,mytbl.addr2,mytbl.city,mytbl.state,mytbl.zip
FROM
Customer_information_table
GROUP BY 
mytbl.addr1,mytbl.addr2,mytbl.city,mytbl.state,mytbl.zip
ORDER BY
Occurrence

this query will bring the top 5 most common addresses.

You could then set a combobox with its rowsource set to this query and its value something like
addr_qry.addr1 & ";" addr_qry.addr2 .... ";" & addr_qry.zip


You could then use a vba sub/function to populate individual textboxes for addr1,addr2,...zip with values from the above combobox using functions like Instr, LEFT, MID etc.,
For example,

delimiter_position = InStr(addr_cmbbox,";")
addr1_textbox = LEFT(addr_cmbbox,delimiter_position)

previous_position = delimiter_position
delimiter_position = InStr(delimiter_position,addr_cmbbox,";")
addr2_textbox = MID(addr_cmbbox,previous_position,delimiter_position - previous_position + 1)

You could repeat the above 3 statements for city, state and zipcode.

There probably is a easier way but I cant think of any.

 

[wemeier]

I'm not sure how you determine your most common addresses. Are these generic addresses or specific to the client? If your most-used addresses are on a table, you could use as the RowSource of your combobox:

SELECT Address, City, State, Zip FROM tblCommonAddresses;

Then, when an address is selected from the combobox, add the following code to the AfterUpdate event:

Me.City = Me.Address.Column(1)
Me.State = Me.Address.Column(2)
Me.Zip = Me.Address.Column(3)

You could also add coding to add a new entry to your mail log table as follows:

Dim db as Database
Dim rst as Recordset

Set db = Currentdb()
Set rst = db.OpenRecordset("tblMailLog",,dbAppendOnly)
rst.AddNew
rst!Client = Me.Client
rst!Address = Me.Address
rst!City = Me.City
rst!State = Me.State
rst!Zip = Me.Zip
.Update
rst.Close
Set db = Nothing

Cruising the Information Superhighway
_{(your mileage may vary)}

 

[eastmanj]

At the risk of sounding stupid, I don't know how to "code" in access. I have made some very simple databases using wizards, basic queries, tables, forms, etc. The database I am working with was designed by someone who knows a lot more about access than I do. However, she is no longer here and I need to update some things. I thank the two who have replied so far. However, if you could walk me through it, I would be eternally grateful. Thanks.

P.S. If you need a better explanation of the form and/or what I am trying to do, please let me know. Thanks again!

 

[wemeier]

The first code snippet in my previous response should be put in the RowSource of your combobox. Select the combobox on your form and choose Properties from the toolbar. Place my code in the RowSource property.

While in Properties, choose the Event tab and select the AfterUpdate event. In the textbox to the right of the event name, select Event Procedure. You'll see an elipsis button (...) to the right of the text box. Press it to bring up the "code behind the form". Between the Private Sub... and End Sub lines paste my second and third code snippets.

Cruising the Information Superhighway
_{(your mileage may vary)}