Updating a form based upon a combo box selection?

[servoss]

Ok, I'm not a very experienced Access user/developer, and I have little to no VBA experience at all, so, please, bear with me...

I have two tables: TableA and Table B. I have created a Form using fields from TableB, which are linked to TableA via the Relationship Tool. In the form, there is a Combo Box who's list is filled via a query on one of TableA's fields.

My question is this: How do I update the form such that the other fields on the form (from TableB) are those associated with the same record in TableB as the Combo Box selection?

I've tried to create an AfterUpdate script that uses a GoTo for the record in question, I've tried to create a TableB query to find the record that contains the ComboBox selection, I've even tried to force things by copying VBA code from the Northwinds example. Nothing seems to work.

I'm certain that this is a relatively common thing to want to do and there is likel a very simple solution - I simply don't know what it is.

Can someone please help me?

Thanks,
Tom

 

[Pat Hartman]

You should not be saving the same data in two places. By your description it sounds like tableA supplies some look up value that you want to display on a form. An example might be displaying a customer name on an order form. The order form would have a combobox with customer numbers and when you select a customer from the combo, you want the customer's info to show on the form.

This is easily accomplished by using an autolookup query. Make a query that joins tableB and tableA. Double click on the join line after you draw it so you can change it to the option that says "select all the rows from tableB and any matching rows from tableA". Select all the columns you need from both tables. Base the form on the query rather than on tableB. Set the locked property to Yes for any fields that come from tableA because you don't want the user to accidentally update them on tableB's form. When you select something from your combo, the related fields will automatically fill with the correct data. You won't need to write any code.

 

[servoss]

Thank, but I think I need more...

Pat,

Thanks for the suggestion. I think I understand the theory behind it and think it will do just the trick.

Unfortunately, I think I need a few more details in order to pull it off. For example, I am able to build the beginnings of the query by using the query designer - I have selected all of the fields that I need from both TableA and TableB and I have set up the relationship as you suggested - but I'm not sure how to actually set the criteria for the query. Likewise, I have created a form based on the query, but am unsure how to link the query to the combo box in my form.

Anyway, you can see that I've still a few roadblocks that I could use some help with.

Thanks, in advance,

Tom

 

[Pat Hartman]

On your new form, change the textbox (you never mentioned a field name) to a combobox. The combo should be bound (have the name of a field from the form's recordsource in its controlsource). When you choose an item in the combo, Access will sync the joined table to automatically select the correct field values.

 

[servoss]

I've got it working...kind of...

Ok, so I've been able to get the form working partially, based on responses to my questions and responses to questions posed by others. But now I'm stuck, again.

What I have is a pair of tables (tblLocations and tblPermits) and a Form (frmPermits). The idea is that for each tblLocation.ParcelNum, there can be 0 or more Permits, whose details are stored in the tblPermits table.

So, I have frmPermits set up such that one selects the Parcel Number using a combobox in the header. The combobox is populated based upon a query, which is based upon the combobox selection, then the rest of the form is filled in if there are already permits assigned to this parcel. In fact, it returns all of the permits assigned, which is exactly what I want.

So far, so good.

The problem is I cannot figure out how to edit permit information for records that pop into the form after selection of the Parcel Number or add additional permits to be associated with the parcel number.

I recall reading in the help pages or a tutorial or somewhere that if there are multiple tables being used to populate a form, then the records become uneditable. I could understand this, but I'm not allowing the ParcelNumber to be edited - only those records contained in tblPermts.

I'm at a loss.

I have a database that I tried to attach, but was told it is over the size limit (zipped it is only 448K). Since I figure it is much easier to actually see what I'm doing than to try to discern it from descriptions, I would be more than happy to send a copy to you. I've stripped out all the extraneous tables and forms and so on and have only left the portions essential to this issue, I think. All of the data is bogus, so don't worry about privacy issues or anything like that.

As I've said, I really appreciate the help, so thank you very much.

Tom

 

[Pat Hartman]

Try using a mainform for tableB information and a subform for tableA information.

 

[servoss]

Thanks!

Well, I gave it a quick try and I think your suggestion has worked nicely!

I know I must still be a novice if you can answer a long-winded question with a single sentence!

Until the next time I don't know what I'm doing....which ought to be later today...

Tom