trying to update table using combo boxes

[timfost]

Folks,

I'm not that familiar with MS Access and was hoping that someone could point me in the right direction to determine the best way to accomplish the following

the situation

1) I have a sales forecasting applicaiton
2) it has a number of cross reference tables that the users
select from when creating a new record in the main data table
3) they want the ability to edit records after they have been entered and saved
4) when entering the record for the first time the users would choose something like sales person from the sales person table and the id of that record is what is saved to the record in the main table
5) I currently have a form that they enter criteria on to retrieve a list of records they may want to update
6) after choosing the criteria a new form is displayed with the records from the main table that match the criteria. They can then update it. But of course because the main table stores the keys to the cross reference tables instead of the actual value what is displayed would be a column named salesperson_id with a value of say 100
7) the users don't know what that is so I need to display the value from the corresponding record in the sales person table
8) I can do this using a query but then they can't edit the record
9) finally, when they edit the record I need them to be able to choose the new values from the cross reference tables instead of asking them to understand that typing in 100 in a column will mean that it is actually sales person john doe

what I was thinking of doing

1) change my form that displays the records to be updated to run a query that pulls in the proper values for each column from the cross reference tables
2) once the users select the record they want to update, the click on a button which opens another form with combo boxes on it that represent each column that can be updated and would by default have the values from the record they are updating displayed.
3) the users choose the new values and click another button - this will build the update statement and run it

Is the above a good way of trying to do it or is there a simpler way in access

thanks in advance

 

[MajP]

If I understand correctly you have ID, but you need to display the data associated with the ID. For example it is an item ID and you want to display the item name.

This is often just done using combo boxes. The combos rowsource would be a query with two fields
itemID
itmeDescription

In your combo box you set the bound column to 1 because that is the ID field that is saved in the table field. But on your control you set the column count property to 2. Then you set your column width to something like 0";1"

Now you only see the description in the combo because the 1st column is 0 wide, but you are saving only the ID.

 

[timfost]

Majp,

let me clarify my issue or question a bit

we have a table called nominations that looks like

salesman_id
terminal_id
amount

now entrying the records works as we want. Users select sales person from combo box, select terminal, enter amount and all is saved to the table - id is saved in place of the value they picked in the combo box

where I am is struggling is the following

1) users may want to edit one of the records at a later date, so I have a form with combo boxes on it that they can use to help select the records they may want to edit. They choose a salesman and a terminal - the submit button opens a form that runs a query using the values they entered as criteria. It displays the records from the table matching the criteria. Instead of displaying the id columns, the query joins on the cross reference tables and displays the values from there instead. This works as I would like it
2) so the list of records they can update looks as expected. They see a terminal name instead of an id and can recognize that that is the record they want to update. When they choose a record to update though I want to limit the values they can enter to the valid values for that field and then need to save the id instead of the value they entered back to the database. My direction was to put another box on the form that displays the record to be updated. This would say something like update and would open up yet another form. The data from the record they have chosen would be displayed in the combo boxes and then they could could choose a different value if they want and hit a save button

My question was really: is the above a good way to do it in access or is there an easier way that it could be accomplished. I basically have to have three forms in order to update a record and not being that famialar with Access I was thinking there might be an easier or mor effiecent way to accomplish my goal

thanks

 

[MajP]

That design is probably fine, there are many ways to do it. Real estate, validtion of data, info flow dictate a ueseable design. If you want to reduce the number of forms you can do a couple of things. One way would be to use a subform.

On the mainform you can use your two combo boxes. Link the subform to these combo boxes. So if you salesman_id 1, and terminal_id 2 you will get in the subform all records assigned to salesman 1 and terminal 2.

Again, you do not need to link the junction table with the salesman and terminal table. You can just return these fields in you subform
salesman_id
terminal_id
amount

If you put a combobox on these ID fields you can display other salesman and terminal fields, but bind the data to the ID field, as I describe in my prior post.

 

[timfost]

MajP,

so if I undterstand what you are saying it is

1) I still have my form where they enter their criteria
2) this opens a form with a filter applied using the criteria that was entered. The form has combo boxes with the id's as the bound columns and displays the name field


I've tried this and see where it will work better than what I was thinking. I'm having some trouble displaying the value from the record in the combobox when the form opens and when they navigate to the next record. I need to display this so that they can recognize the record they want to update. The criteria they enter does not limit it to one record to update. I was using a dlookup in the default value of the combo box in the previous way I was doing this and it seemed to work but I can't get it to work now. Any thoughts on how to do this

thanks for all the help

 

[MajP]

I'm having some trouble displaying the value from the record in the combobox when the form opens and when they navigate to the next record. I need to display this so that they can recognize the record they want to update. The criteria they enter does not limit it to one record to update. I was using a dlookup in the default value of the combo box in the previous way I was doing this and it seemed to work but I can't get it to work now

Can you explain this better. I am not following what you are saying?

I think if this was me (and if I understand what you want), I would use one form with 2 linked subforms.

I would have two combo boxes on top of my form linked to my subform 1. If I select Salesman 1 and terminal 2 the continous subform, "subform1", lists all records for Salesman 1 and Terminal 2. Subform1 would be a continous and not editable. I would have a second subform below the continous Subform1, (Subform2). Subform2 would be linked to subform1 so that as I click on a record on subform1 it appears in subform2. Subform2 would have editable fields and probably more fields than subform1. There are a couple of FAQ on how to do this.

So on 1 form the combos control the filter for subform1 and subform1 allows you to select a single record to edit.

Subform1 could also be a listbox instead of a subform. A little easier to format and control the second subform.

 

[timfost]

actually what I did is the following and it seems to be working okay - although subforms might be a better way to go

1) one form where they enter their criteria and hit submit
2) this opens the other form that has it's record source set to the nominations table and applies a filter to it
to limit the records displayed to just the correct ones
3) the second form has combo boxes on it that have as their control source the id field from the table and their row source type set to table/query and their row source set to a query that returns the name and id from the cross reference field

so for example

nominations table has terminal_id
cross reference named terminals has terminal id and terminal name

my combo box has nominations.terminal_id as the control source. it has row source type set to table/query and row source set to SELECT Terminals.[TERMINAL ID], Terminals.[TERMINAL NAME] FROM Terminals; the column count is 2 and the bound column is 1 with column widths set to 0,1

so this displays the value in the way they want it and when they change it and hit save it saves the id back


thanks