Combo Boxes in a Form change when entering new data

[FaurKris]

I have a form with fixed list boxes which are of the persons name and address. Below those, I have Combo Boxes so I can manually enter data like phone number, email, etc. If the information is already there, I simply review and print the Report. If not, I add the data.

My problem is that after I manually enter data into a Combo Box, upon selecting the next Combo Box, the fields (All of them) change. What seems to happen is that the form reverts to the first listing (alphabetical) rather than allowing me to simply continue filling out the information I need. So I am having to enter data in one box and then I have to scroll back thru the listings, find the selction I want to work with, enter more data, and repeat for each Combo Box.

Is there a way to keep the form from changing so I can remain on the selection I want, fill out the blanks (or make changes) and go to the report?

Note: Most fields from the Combo Boxes are blank in the table and they will change often.

Thanks.

[jimatqsi]

What events are coded to do some action? Something in the After_Update event of each textbox?

Jim

[FaurKris]

Private Sub Combo40_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Payment] = '" & Me![Combo40] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[jimatqsi]

So no wonder the combo boxes, and every other data item on the form, are changing when you change the value in the combobox. This code is going out to look for the first record that matches the [Payment] to the entry in the combo box. If all the combo boxes have similar logic, then every time you change any combo box the form possibly changes to a different record.

You want this sort of code only on the combo box that you are using to look up a record; after you look up the record you want to edit it without switching to a different record. Your lookup combo box should not really be bound to the record being looked up, it should be an unbound object.

Jim

[FaurKris]

I've literally just begun teaching myself Access. Can you suggest what the code should read? Please?

[jimatqsi]

What version of Access are you working with? There should be a sample database loaded on the system, it was loaded automatically with Access. It's called Northwinds.mdb. That has a lot of sample code, some of it illustrating just what you are trying to do here.

The first thing to change is to change the properties of the combo boxes that you search with. They should not have any data source, that binds the box to the data record, so when you change the record, the combo box changes. Make some text boxes and bind those to the data source.

Just based on what you have so far, it seems you made good progress teaching yourself. But spend some time reviewing the code in the NW database, there's a ton of good stuff there.

Don't worry, we're here to help when you get stuck. :)

Jim

[FaurKris]

@jimatqsi
Thanks. Now that I have replaced everything with a text box, I am able to fill out the form properly and it doesn't change like before.

BUT........... Now the report is not showing the information I put into the form? The data is being stored tho.

[FaurKris]

Edit: If I scroll away from the latest information added or edited and then come back to it, everything prints to the report as it should. However, if I simply go to the report immediately, the information is not shown.... Hmmmmmmmmmmmm.

[jimatqsi]

Are you saying you can still use your combo boxes to look up a given record, and that works okay now? Have you gone into the table itself to verify that the table is being updated as it should? Sounds like you did that.

If you have andif the data is correct, maybe you should start a new thread for your reporting problem. Give some details about what you are trying to report and what is happening with it. But tell us either way if the table is looks like it should.

Jim

[FaurKris]

@jimatqsi
Sorry for the confusion. No, I erased all the combo boxes and such and simply did a drag-n-drop from the Field List to use text boxes as you suggested. It allows me to edit the information stored in the table without changing on me.

I went in a chacked the table and sure enough, all the data is being stored. But unless I literally "scroll" to another entry and back, the report will not print the current (newly added info) selection. Or if I close and re-open the form. Almost like when I click the button to preview the report, the form isn't being refreshed to recognize the new data.

Sorry to say, I was not able to discover something you experts are unaware of... :)

[jimatqsi]

Very good, FaurKris. It just sounds like you need to write the last changes prior to launching the report. Add this just before the code that launches the report

if me.dirty then me.dirty=false

That says if the form has been changed then update the database with the change.

Jim

[FaurKris]

@jimatqsi
PERFECT! Thanks a bunch!