bound combobox UNWANTED save when closing form 2

[xuanb]

Hi, I'm a newbie in Access.
I have 3 bound comboboxes and other bound textboxes and radio buttons on my form. When I select an item in a combobox and close the form it overwrites the previous record in the database. This is NOT a desired behaviour. Here's an example when I select Region 2 and close the form.

BEFORE AFTER
cboRegionName cboRegionName
Region 1 Region 2
Region 2 Region 2
Region 3 Region 3


You may ask why I don't make the comboboxes or the entire form unbound and use Recordset.Clone with a bookmark. Reason is I want to use the same form to enter new records when clicking the >* button at the bottom...ye know what I mean? Here's how I load my form.

Private Sub Form_Load()

'Combobox 1
Me.cboRegionName.RowSource = "SELECT DISTINCT sqlFormRS.RegionID, sqlFormRS.RegionName " & _
" FROM sqlFormRS " & _
" ORDER BY 2;"

Me.txtRegionID = Me.cboRegionName.Column(0)

'Combobox 2
Me.cboPrisonName.RowSource = "SELECT DISTINCT PrisonID, PrisonName " & _
" FROM sqlFormRS " & _
" WHERE sqlFormRS.RegionID = " & Me.txtRegionID & _
" ORDER BY 2;"

'Combobox 3
Me.cboFacNo.RowSource = "SELECT FacilityID, FacNo" & _
" FROM sqlFormRS " & _
" WHERE PrisonID = " & Me.txtPrisonID & _
" ORDER BY 2;"

Me.txtFacID = Me.cboFacNo.Column(0)

'Text fields
Me.txtInspectorName = DLookup("[InspectorName]", "sqlFormRS", "[FacilityID] = " & Me.txtFacID)

....

End Sub

So what can I do to prevent the comboboxes to autosave on a selection? Thanks for your help!

 

[jebry]

Hi!

What are you using these combo boxes for? It sounds like you want to use them to filter or search the recordset. If that is the case then you must make the combo boxes unbound or you will always get behavior like what you describe. My recommendation would be to use separate combo boxes to do filters or searches and use bound text boxes to enter and display information. You can use a rectangle control to set off the search area of the form or put it in the footer or header.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[judgehopkins]

xuanb, if you want to disable the autosave feature on a form, try checking out these threads:

thread702-539801
thread702-506348

In my never-to-be-humble opinion, however, jebry has given you the answer. Cutting off the autosave is a real pain for your users.


Judge Hopkins

"...like hunting skunks underwater...."
John Steinbeck, The Grapes of Wrath

 

[xuanb]

Thanks for your replies.
Yes, I use the 3 comboboxes to filter Facilities by Prison and Prisons by Region
Regions -> Prisions -> Facilities

Hmmm, can you please clarify, jebry?

1. Make comboboxes unbound
2. Transfer and display the selected item in a bound textbox

Please tell me if this is what you mean.

Thanks judgehopkins for the 2 threads. I will investigate that option as well.

 

[jebry]

Hi!

When you select a region to filter the form by then the current record will automatically have its region changed. That is why combo boxes used for filtering need to be unbound. Yes you can code your way around it but I think it is easier to the built in facilities offered by Access unless there is a compelling reason not to.

Now, if you are using the combo boxes to select regions prisons and facilities for new records or to edit existing records then there is no problem with having them bound. But the users must be carefully trained to understand that everytime they make a new selection from one of the boxes that they are changing the record.

One thing you cannot do with a combo box is combine the functions. If you use the box for selection do not use it to filter the form. You are bound to lose information that way. There is no harm in setting up bound text boxes or combo boxes to enter and display information and have another set of combo boxes to filter the form. From your original post I got the idea that you are already doing it this way except that you have bound the combo boxes and they must be unbound for this to work.

Finally, your last post makes it look like the only filtering you are doing is from one combo box to the next. This should not be causing the problem you are seeing assuming that you are making the selections on the record where you actually want to store the data. The row source of the combo box shouldn't be effecting the current record of the form. One method you might try to avoid any problem is to set up region, prison and facility tables to pull the information from.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[xuanb]

Jebry,
The odd thing is, yes, I am making the selection on the record I want to store. The property for the combobox is (same for all 3)

cboRegionName
Column Count 2
Bound Column 2
Column Widths 0";1"
RowSource SELECT RegionID, RegionName...

Regions, Prisons and Facilities are coming from 3 different tables. So looks like I'm doing everything you're saying. But I'm still at odd with
"The row source of the combo box shouldn't be effecting the current record of the form." :-(
If you have an answer, great. I say thank you with a star.

 

[jebry]

Hi!

Maybe you can post your table structures because you have gotten me curious. You say that the three ID's are in different tables, and that is probably best but, according to your first post, the row source of each combo box is reading the same query. Maybe you can can change the row source of each combo box to get the information directly from the three diffent tables.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[xuanb]

Yes, you have keen eyes. The RowSource of the combos are based on sqlFormRS, the record source query of the form. sqlFormsRS is pulling columns from tblRegions, tblPrisons, tblFacilities that are joined by their IDs.

tblRegions tblPrisons tblFacilities
RegionID PK PrisonID PK FacilityID PK
RegionName fkRegionID fkPrisonID
PrisonName FacilityNumber

Whether I make one of the tables or sqlFormsRS the RowSource of the combos the overwriting behaviour is the same.