Update Statement or query from VBA

[Recommender]

I am trying to update the fields of a table from comboboxes and textboxes on a form. I don't want all the fields to receive data from the objects on my form directly. Some of the bound controls are not visible and data would be entered into them from visible comboboxes instead. I am not sure exactly how to do that. I tried to devise a query that would take the values from the comboboxes and equate them to those of the hidden controls, but that didn't work. I tried inserting an update command in code but that came up with an error. If some one could help me I would be thankful.

 

[Pat Hartman]

Unbound forms require a great deal of code and an understanding of what actually needs to happen to display and update records. Is there some reason that you think that bound forms won't work for you?

 

[Recommender]

There is no obvious reason that I can think of that makes unbound controls useless for me. On the contrary, I think mixing bound and unbound controls on my form would make the database easier to use.

 

[RV]

On the contrary, I think mixing bound and unbound controls on my form would make the database easier to use.

I don't know why and where you got this opinion from.
Using bound controls would be the best way to deal with applications built in Access.

The most important requirement to make your life as easy as possible is to define your database structure using the principles of normalization.
This helps you also to minimize any VBA code behind your forms to maintain your tables.

As for your forms, you should base these on queries.

RV

 

[Recommender]

I don't know why and where you got this opinion from.
Using bound controls would be the best way to deal with applications built in Access.

I just thought it would be simpler for users who will use my application to select the values from comboboxes on my form instead of entering the data manually.

 

[RV]

You could consider lookups.

RV

 

[Pat Hartman]

Combo boxes are the optimum tool for choosing data. If your question is how to display data related to the item chosen in the combo, the answer is base your form on a query that joins the two tables. Take a look at this Example to see the three most common methods - join (best), columns of the combo, DLookup() (worst).

 

[testmud]

Write the belows VBA Code in combo box of afterupdate() method,
change the recordsource in the form then data also will change too.

Private Sub cboLot_Number_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT * FROM [In Stock] WHERE [Lot Number] like '*" & cboLot_Number.Value & "*'"
Me.RecordSource = strSQL
cboLot_Number.SetFocus
cboLot_Number.Value = ""
End Sub