Use a form to update a table

[Jollywg]

I've got a simple form with one dropdown box and 2 text boxes. the drop down box chooses a sales territory and then one of the other text boxes automatically shows whose in the sales territory shown. All the info is being pulled from one table. I'm having a lot of trouble figuring out how to take user input out of the second text box (which would be the new sales rep) and replace the old sales rep in the table. Any help would be greatly appreciated!

[Scott Price]

Unless there's more to your problem than you mention, there is no need to use two text boxes to accomplish this! Just make sure that the control source of the text box is set to the field in the table that contains your old sales rep. Then whatever changes the user types in are automatically stored in that field.

However, if you wish to reduce possible user entry errors, a second combo box that shows available sales reps should be used so that the user can simply select the rep from the combo box instead of having to type the name in.

Regards,
Scott

[Jollywg]

Scott,

Thanks for the response, but when i set the dropdown box's Control Source to something it does not let me choose a sales territory from the list, so i have it blank and the Row Source to [tblTerritory]. I have the one text box control source set to the salespersons name, but when i go to type in anything the status bar says "Control can't be edited; it's bound to the expression '[tblTerritory]![Sales_Name]". I get where your going with this, but i dont know how to relate the territory number to the corresponding sales name so i know that i'm changing the correct salesperson, instead of the first person in the table.

[Scott Price]

The accepted way of doing this is to have an 'unbound' combo box like you have. It's control source set to nothing, the row source set to something like this:

SELECT SalesRegionID, SalesRegion FROM tblSalesRegions

This means you now have two columns in your combo box. Set the bound column to column number 1 (the ID column). Then set the Column Widths to 0", 1.5" (or wider if needed). Now when you select a sales region from the combo box, it returns a number corresponding to the ID value of that region.

Make a query in design view that combines the SalesRegions table with the SalesPersonnel table (if you don't have the equivalent of these two tables, you need to restructure your database. See Normalisation and Table structures). Now you set as a WHERE criteria for the SalesRegionID in your query a reference to the combo box result. I.E.

WHERE tblSalesRegions.SalesRegionID = Forms![FormName]![ComboBoxName]

.

Now you go back to the form in design view, double click the small square in the upper left corner to bring up the Properties dialog box, and set the Record Source for the form to the query you created. Go to your text box and set the Control Source for your text box to the field corresponding to the Sales Person Name. You'll need another text box (Set it's Visible attribute to False) with Control Source set to the ID field for the SalesRegion. Now whatever changes you make in the text box are written through the query into the table.

Setting the other combo box up will be similar. Set it's Control Source to nothing (leave blank in other words). It's Record Source will be something like:

SELECT SalesPersonName FROM tblSalesPersonnel

. It has only one column, so will be a bit different from the first combo box. Now you will write the simple code to make the selection in this combo box reflect in the text box. In the Properties dialog box for the second combo, click the ellipsis (...) to the right of the After Update event on the Events tab. Choose Code Builder. This will put you into the VBA Editor window. Between the lines that say Private Sub [ComboBoxName]_AfterUpdate() and End Sub write code like this:

Me.[TextBoxName] = Me.[ComboBoxName]
Me.Dirty = False

If this is too simplified, I apologize...

Kind Regards,
Scott

[Jollywg]

Scott,
That is almost exactly what i needed. The only problem i am having is with the two text boxes. I have the invisible text box's control source set to SalesRegion and the visible text box set to Salesperson and the form's RecordSource is set to "tblTerritory" (which holds the salesRegion and salesperson name). Whenever i go to test it, the combo boxes work together, but the textboxes are not updated. I even resorted to just typing in a sales region and a salesperson, but then it gives me an error "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." We're almost there.

Thank you so much

Matt

[Scott Price]

What is the current structure that you have? By structure I mean the table names, fields, primary keys, form names, combo box names, text box names, control sources, etc.

Regards,
Scott

[Matus1976]

Hi I'm having a similiar issue though probably much simpler as I am newbie to access. I have a form which updates my table just fine except for some fields in the form are expressions, and the results of the expressions do not populate the table, but the form always remembers the results of the expression. also if I open the "datasheet" view it looks like the table but with the results of the expressions present in the appropriate fields.

[Scott Price]

Matus1976,

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See How to Ask a Question).

MODERATOR

[Matus1976]

Matus1976,

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours.

MODERATOR

My apologies I was not trying to hijack the thread, I started a new thread with my question. Can these posts be deleted?

Thanks

We'll leave it as is. No worries, and thanks for starting your own thread.

Welcome to the Scripts!

Regards,
Scott

[Jollywg]

I have 3 tables tblZipCode, tblManager, tblTerritory

The table i've been trying to update is tblTerritory

It's made up of:

tblTerritory(Territory_Number; Salesperson; Manager_Name; District_Name;)

tblZipCode(ZipCode; Territory_Number; Num_Sales_Outlets;)

tblMangaer(District_Name; Manager_Name)

On the form I have 2 text boxes and 2 comboboxes.

cboCurrentTerritory is the combobox that has two columns in it.
cboCurrentSalesperson is the combobox that reflects the salespersons name from the territory.

*the comboxs work correctly*

txtNewSalesperson I have cleared out the attributes
txtTerritory is the hidden textbox, but other than that i have cleared out the attributes

Matt

[Scott Price]

It looks like you just need to bind your txtNewSalesperson to the SalesPerson field in the table. By 'bind' I mean set it's control source to that field.

Regards,
Scott

[Jollywg]

I'll give that a try, but the last time i did that it said that i could not change the contents because the control source was set to ".....".

[Jollywg]

Scott,
Thanks for all your help, I finally got it.

Matt

[Scott Price]

Glad you got it working! I just got back to computer access from a few days on the road.

Regards,
Scott