Split form, all records get the same value

[avtuvy]

My Access 2007 application includes a split form, I assign a value to one of the fields in one of the records prorammatically using VBA. For some reason as soon as I do that all of the records get that value diplayed in that field, how can I fix that problem?

 

[dhookom]

It sounds like the "one of the fields" is an unbound text box. If not, could you share how you "assign a value to one of the fields"?

Duane
Hook'D on Access
MS Access MVP

 

[avtuvy]

That is correct, the field is unbound and I assign a value using VBA. The reason that I am going through that excersize with VBA is that I need to set it so onle when that user clicks on a save button the data will be saved but with the default setup of the form as soon as the user exits the form data is saved and I need to change that behaviour, is that possible?

 

[dhookom]

What are you attempting to accomplish?
How are your forms set up?
An unbound control doesn't save a value to any table.
An unbound control on a continuous form will display the same value in every record in the form.


Duane
Hook'D on Access
MS Access MVP

 

[avtuvy]

my app is a front end to a SQL database and it's purpose is to allow users to manipulate data in the SQL db. The data source is a query which does a JOIN of several tables and I want to build it in a way that only when the user clicks on the submit button data will be saved. I built the first generation of this app using Access 2003 using VBA and was able to do that but I did not have the data sheet view of the data which I like ot add to the second generation.

 

[dhookom]

I am still very confused about your forms and controls and tables and fields and record sources and what you expect to happen.

Duane
Hook'D on Access
MS Access MVP

 

[avtuvy]

I have a split form with a query as a data source, several text boxes are bound to fields in the query. Records are displayed in a sheet form on the bottom and text boxes on the top. I am able to scroll through the records by using the arrows on the bottom of the form. Users make chnges to the data in text boxes or adding records, and I want to be able to save data by clicking and only by clicking on a submit button, before data is saved it needs to be handled by VBA code. right now it seems like data is saved as soon as the user changes it or when the user quits the form because the controls are bound. I guess the main question is how to trigger data save when a button is clicked.

 

[dhookom]

Bound forms will save data when the form loses focus or moves to the next record. If for some reason you don't want this, you may need to create an unbound form to put values into text boxes. Then add a button that inserts a new record or updates an existing record.

I'm not sure there is a good reason to do this.

Duane
Hook'D on Access
MS Access MVP

 

[avtuvy]

I do want this feature and it makes development much easier, the problem that I have is with a combo box in my form below is the scenario:

1. Split form
2. several tgext boxes
3. one combo box
4. table_1 columns: town_id, town_name (lookup table for towns)
5. table_2 columns: col1,col2, town_id
6. all controls are bound to the query

My goal is to display the town name for the current record in the combo box which I am able to do and display a list of towns when the box drops down from table_1. when the user selects a town I want the town_id of that town to be saved in table_2. what happent in my case it saves the town_name in table_1. It seems like a common scenario but I cannot get it to work correctly.

 

[avtuvy]

Having text boxes bound is the best solution but it causes a problem with storage of data from a combo box and I am looking for a simple solution, here is the description

My application includes the following:
1. The same split form as previously described
2. several textboxes
3. Combo box

table_1 includes town_name, town_id
table_2 includes col1,col2,town_id

all of the controls are bound to a query which does a join of table_1 and table_2 so the output of the query includes all of the columns in table_2 and the columns in table_1.
my goal is that the controls on the form show data from table_2 and the combo box shows town_name (from table_1) when I click on the combo box I want to be able to see all town names from table_1 and select a town by name, when the record is saved I need the town_id which corresponds to what is selected on the combo box to be saved in table_2. the problem that I am having now is that it saves town_name that is selected in table_1.