Combining data from two tables (1 Viewer)

L

Litmol

New member
Local time
Today, 07:33
Joined
Feb 2, 2019
Messages
9
Hi all,
I would appreciate your help with the following issue (presented in somewhat simplified way):
I’ve got two tables: tblTrainees and tblObjectives.
I’m entering data to tblTrainees via frmTrainees. One of the fields in the form is Subject – its Row Source is tblObjectives and the Row Source Type is Table/Query.
tblObjectives has the following fields: ID, Subject, Objective1, Objective2.
I’ve got a very simple query that polls the Subject from tblTrainees and Objective1 and Objective2 from tblObjectives. The problem is that the query doesn’t present any values for Objective1/2 although they have values in tblObjectives.
What is more puzzling is that the query works fine for "historical" data in tblTrainees (by "historical" I mean data that I imported into the database from an Excel file) – the problem occurs ONLY for data which I recently entered to the database via the form.
Any idea?
Thank you in advance for your support​
 
essaytee

essaytee

Need a good one-liner.
Local time
Today, 14:33
Joined
Oct 20, 2008
Messages
512
Litmol said:
Hi all,
I would appreciate your help with the following issue (presented in somewhat simplified way):
I’ve got two tables: tblTrainees and tblObjectives.
I’m entering data to tblTrainees via frmTrainees. One of the fields in the form is Subject – its Row Source is tblObjectives and the Row Source Type is Table/Query.
tblObjectives has the following fields: ID, Subject, Objective1, Objective2.
I’ve got a very simple query that polls the Subject from tblTrainees and Objective1 and Objective2 from tblObjectives. The problem is that the query doesn’t present any values for Objective1/2 although they have values in tblObjectives.
What is more puzzling is that the query works fine for "historical" data in tblTrainees (by "historical" I mean data that I imported into the database from an Excel file) – the problem occurs ONLY for data which I recently entered to the database via the form.
Any idea?
Thank you in advance for your support​
Click to expand...

After closing the form, then opening your form, do you see your data correctly displayed? If so, sounds like you may need to force a requery on your combobox or listbox eg. Me.frmYourForm.yourListBox.Requery (this would be done from the AfterUpdate event).
 
L

Litmol

New member
Local time
Today, 07:33
Joined
Feb 2, 2019
Messages
9
Yes, data entery from the form is OK. If I open tblTrainess the Subject field (and all other fields) has data in all records (I later present the Subject field in a report and it's presented perfectly.
The problem is probably in the linking of the two tables.
 
essaytee

essaytee

Need a good one-liner.
Local time
Today, 14:33
Joined
Oct 20, 2008
Messages
512
Litmol said:
Yes, data entery from the form is OK. If I open tblTrainess the Subject field (and all other fields) has data in all records (I later present the Subject field in a report and it's presented perfectly.
The problem is probably in the linking of the two tables.
Click to expand...

Just to be clear, initially when you are inputting your data from the form, the form does not display some fields (Objective1 and Objective2). Upon closing the form, then re-opening the form, navigating to the record that you previously entered, the two fields now display correct data?
 
L

Litmol

New member
Local time
Today, 07:33
Joined
Feb 2, 2019
Messages
9
I hope that the following explanation will make my issue clear.
The form is used to enter data to tblTrainess. One of the fields in the form (and thus in the tblTrainees) is Subject. In the Form (or in tblTrainees) there are NO fields name Objective1/2. All data entered via the Form is stored correctly in tblTrainees.
Objective1/2 are two fields in tblObjectives. This is a table that I rarely change. It includes the Objectives for each Subject. Subject is the common field for tblTrainees and tblObjectives.
I’m trying to use a query to join these tables, so I could present the Objectives for each Subject.​
 
L

Litmol

New member
Local time
Today, 07:33
Joined
Feb 2, 2019
Messages
9
[FONT=&quot]Let me add one more thing that might be useful. I think that the issue is related, in some way, to the fact that the Row Source Type of the Subject field is a Table/Query. I made a little experiment and changed the Row Source Type of the Subject field to Value List. In that list I included some of the values listed in tblObjectives (which was the Row Source of the Subject field) and the query works fine!![/FONT][FONT=&quot][/FONT]
 
essaytee

essaytee

Need a good one-liner.
Local time
Today, 14:33
Joined
Oct 20, 2008
Messages
512
If you can, upload your db, a stripped down version, so that we can see what the issue is.
 
L

Litmol

New member
Local time
Today, 07:33
Joined
Feb 2, 2019
Messages
9
The database is in an isolated system which is disconnected from the Internet. I'll make a simplified version of it and upload ipwr your suggestion.

Thanks
 
L

Litmol

New member
Local time
Today, 07:33
Joined
Feb 2, 2019
Messages
9
Hi all
It seems that I made some kind of a mistake when defining the Subject’s lookup properties. After redefining it and manually fixing the data which was entered using the form, everything is working fine.
Thanks for anyone who tried to help.​
 
You must log in or register to reply here.

Similar threads

Z
2 Tables
Replies
2
Views
187
zezo2021
Z
G
Append query on linked tables via Access Runtime
Replies
9
Views
384
Giammarco_F
G
M
Append new data into Both Parent and Child
2
Replies
24
Views
802
mounty76
M
B
Solved show data from previous year
Replies
11
Views
378
BHWATT
B
E
update table field from another table
Replies
8
Views
552
plog
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom