Connecting different tables in a formand creating new records

[mackyrm]

I would like to take a list of records in a table (table A), visible on a form, and connect them to a dropdown result and put this in another table (table B), creating new index record if you like in table B. Table A is locked and cannot be modified to add a new field. Is there a way to show a list of records and have a dropdown option beside that record that creates a new record in table B recording the key of the person in A and the status of this record in Table B? e.g. Table A is a list of people, table B allows a person top be classified into a single category and the person key and category are recorded in Table B, when a category is selected.

 

[James Deckert]

Define the field in table B using the Lookup Wizard as the Data Type. It will let you select valid data from another table.

 

[mackyrm]

Define the field in table B using the Lookup Wizard as the Data Type. It will let you select valid data from another table.

Thank you for the reply. The problem here is that the record has not been created in Table B yet at this stage. The joining record between Table A and Table B does not yet exist. What I would like to try and do is generate a list from table A, and in some way select some of these records and assign a status, and put the person ID and the assigned status in table B.

 

[James Deckert]

So there would be a one to many relationship between table A and B? This sounds like you want Table B to have foreign keys referencing table A. Then you open a form with table A at the top and table B being a subform. Then the foreign keys will automatically be entered in table B.
I'm confused by your terminology. Records don't join tables, fields do. So I don't understand your statement about the joining record not existing. Then you're going to select some records from Table A, do you mean "fields"?
Maybe you could provide some examples.

 

[mackyrm]

So there would be a one to many relationship between table A and B? This sounds like you want Table B to have foreign keys referencing table A. Then you open a form with table A at the top and table B being a subform. Then the foreign keys will automatically be entered in table B.
I'm confused by your terminology. Records don't join tables, fields do. So I don't understand your statement about the joining record not existing. Then you're going to select some records from Table A, do you mean "fields"?
Maybe you could provide some examples.

Thanks for the reply. The relationship is really one to one. I cannot update TABLE A, it is from another locked database, I can only reference its contents. So what I would like to do, is take data from TABLE A, represent it as a list on a form, and have some additional related editable fields from TABLE B associated with each of those records, or a selection of those records.

I would like to try and represent this as a list of persons that can be quickly updated on those TABLE B fields.

Any suggestions?

 

[James Deckert]

Ok, I think I'm understanding better now.
With a one to one relationship you just need to include both tables in a query (Record Source) which is behind the form. If the form is in datasheet view, you'll be able to see several people and the fields associated with them (from table B) and the user won't know that some data is from A and some from B. The fields for table B will be editable.

 

[mackyrm]

Ok, I think I'm understanding better now.
With a one to one relationship you just need to include both tables in a query (Record Source) which is behind the form. If the form is in datasheet view, you'll be able to see several people and the fields associated with them (from table B) and the user won't know that some data is from A and some from B. The fields for table B will be editable.

Yes, we are getting there, thanks for your follow up. However, the record in table B may not exist yet, and it is only by looking at a list for Table A, that they will decide if a field needs to be added in table B (it will probably create the instance of the record), in relation to the record table A - if that makes any sense

 

[mackyrm]

Will this solution require a way to build relationships via the forms, perhaps using a listbox for eligible clients, selecting them and pressing a button, then doing an update query of sorts??

 

[James Deckert]

You can certainly do it the way you asked. Did you try it the way I suggested, with a datasheet combining both tables? This will certainly be easier unless it doesn't work for you, for some reason.