Set Control Source of a Combo Box and Text box From other Table (1 Viewer)

Pac-Man

Active member
Local time
Today, 17:11
Joined
Apr 14, 2020
Messages
408
Hello,

I've a form and its control source is Table1. On the same form, there is a cbo and text box which I wish to have control source from other table i.e. Table 2. I also want to add that there is also a subform on the form with control source SubTable. All the three tables i.e. Table 1, Table 2 and SubTable have relationship so that RecID in Table 1 is FK in other two tables.

I have tried using Select query (in which Table 1 and Table 2 are used) few week ago as control source of the main form but it gave me error something like RecID does not exist. Then I used unbound fields on the main form and used INSERT query to insert values in Table 2 but now I'm again having issues with this method so now I am trying to figure out if I could use some code to set control source of few fields on my form to other table i.e. Table 2.

Best Regards
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:11
Joined
Oct 29, 2018
Messages
21,357
Hi. If Table2 and SubTable are children to Table1, you should be able to just use two subforms, one for each child table, on your main form.
 

bob fitz

AWF VIP
Local time
Today, 12:11
Joined
May 23, 2011
Messages
4,717
Open the form that is bound to Table1 I design mode. Use the wizard to add a subform taht is bound to Table2. Also use the wizard to create the combo box.
 

Pac-Man

Active member
Local time
Today, 17:11
Joined
Apr 14, 2020
Messages
408
Thanks for reply. Actually one text box and cbo are on the start of main form while other text box at the end. If I add a subform for Table 2 then these three fields might have to be together in the subform whereas I want two of them at start and third one at the end in my form design.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:11
Joined
Feb 19, 2002
Messages
42,970
I think we need a picture.

Just FYI, the ControlSource of ALL controls on a form are either unbound or bound to the table/query of the Form's RecordSource.

Listboxes and Combos have two binding points. Their ControlSource is bound to the Form's RecordSource but their RowSource can be an embedded list of values or bound to a different query/table.
 

Pac-Man

Active member
Local time
Today, 17:11
Joined
Apr 14, 2020
Messages
408
That database is in office and I'm on leave. I try to recreate it in home and will post it.
 

Pac-Man

Active member
Local time
Today, 17:11
Joined
Apr 14, 2020
Messages
408
I think we need a picture.

Just FYI, the ControlSource of ALL controls on a form are either unbound or bound to the table/query of the Form's RecordSource.

Listboxes and Combos have two binding points. Their ControlSource is bound to the Form's RecordSource but their RowSource can be an embedded list of values or bound to a different query/table.

Dear @Pat Hartman, Please see the attached db. It is almost similar to what I have in office. Currently I have set recordsource of the main form a query consisting of both tables but when I change the entry in the subform, it gives me error message. This error is the main reason I tried and looking for other methods. Create a new record in the main form and then change value in employee subform and see the error message.

Thanks and regards
 

Attachments

  • ReportDB.accdb
    1.6 MB · Views: 152

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:11
Joined
Feb 19, 2002
Messages
42,970
The problem is occurring because the query you are using for the main form uses select * on two tables and ReportID is part of each table. Therefore, to disambiguate the reference, Access brings them into the query using tblReports.ReportID and tblRevisions.ReportID. For some reason, maybe a bug, the name tblReports.ReportID is not acceptable to the master/child links an that is what is generating the error message.

I fixed the problem by changing the query. I left the Select * for tblReports and changed to using specific field names for the other table and I did not select ReportID from tblRevisions since it isn't necessary.

I would use the Feedback button to send the database to MS if possible and explain what caused the problem. I think that if Access allows the column to be selected twice and chooses the tablename.fieldname method to disambiguate, all parts of the application should be on board. I've never run into this problem because I normally don't use Select *. I specifically select the columns I want and I know that I only need to select the ID from the table I am updating and not from both. It is much more efficient when working with RDBMS data sources so I just use the same technique all the time regardless of what by BE is linked to.
 

Pac-Man

Active member
Local time
Today, 17:11
Joined
Apr 14, 2020
Messages
408
The problem is occurring because the query you are using for the main form uses select * on two tables and ReportID is part of each table. Therefore, to disambiguate the reference, Access brings them into the query using tblReports.ReportID and tblRevisions.ReportID. For some reason, maybe a bug, the name tblReports.ReportID is not acceptable to the master/child links an that is what is generating the error message.

I fixed the problem by changing the query. I left the Select * for tblReports and changed to using specific field names for the other table and I did not select ReportID from tblRevisions since it isn't necessary.

I would use the Feedback button to send the database to MS if possible and explain what caused the problem. I think that if Access allows the column to be selected twice and chooses the tablename.fieldname method to disambiguate, all parts of the application should be on board. I've never run into this problem because I normally don't use Select *. I specifically select the columns I want and I know that I only need to select the ID from the table I am updating and not from both. It is much more efficient when working with RDBMS data sources so I just use the same technique all the time regardless of what by BE is linked to.
Thanks for reply. Actually ReportID from tblRevisions is required as cboRevReportNo is bound to it. From your reply that same reportID is causing problem, I tried changing the field name in tblRevisions and it stopped giving me that error. There is one more problem I am having when I select query as recordsource of form. Even though the form is set to allowedits yes but even then form open with first report loaded (that is allowedits seem to be set to No). How to get this problem sort out.
 

Users who are viewing this thread

Top Bottom