MS-Access: Entering data via a query, how can I auto populate a column?

Question

I'm entering data by using a query on a subform. I want to auto populate a job number to each new line I add to that subform.

tbl_Jobs
[Job_ID] <Primary Key
JID1001
JID1002
JID1003

tbl_Job_Tasks
[ID Auto][Job_ID][Task]
1        JID1002  TASK1
2        JID1002  TASK2
3        JID1002  TASK3
4        JID1001  TASK1

Here's my query at the most basic level...

SELECT tbl_Job_Tasks.[Job_ID], tbl_Job_Tasks.[Category], tbl_Job_Tasks.[Task]
FROM tbl_Job_Tasks
WHERE (([Job_ID]="JID1002"));

What I want to be able to do is hide the Job_ID column and have it auto populate for the user if they decide to enter a new task for their Job. Currently if I enter a new task I have to specify the Job_ID to use. This query is being used on a sub form that pulls its Job_ID from the parent form, so in a perfect world the end user would not have to re-enter the Job_ID.

Is this possible?

Thank you,

Is Job_ID being used as your parent/child relationship to the parent form. Just use tbl_Job_Tasks as the source for your sub, and let Access take care of it. — geeFlo
– geeFlo, Commented Mar 24, 2017 at 12:52
Job_ID is the relation holder, but my real query is doing a bunch of sorting of the tasks before it displays them. Just using this as an example since it's a little simpler. — Robomato
– Robomato, Commented Mar 24, 2017 at 12:57

Minty · Accepted Answer · 2017-03-24 14:58:27Z

1

If the subform is linked to the parent form correctly (Using the Link Master/Child Fields property) the child records foreign key (in your case tbl_Job_Tasks.Job_ID) should be filled out automatically.

You shouldn't even need the query unless you are filtering the sub forms records in some odd way.

Edit - Have a look here http://www.access-diva.com/f2.html for a solution to your unbound problem.

edited Mar 24, 2017 at 14:58

answered Mar 24, 2017 at 12:51

Minty

1,6261 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Robomato Over a year ago

Yes I'm doing custom sorting. For the sake of easy understanding I pulled out all the sorting I'm doing to sort the tasks into different categories and orders.

Minty Over a year ago

That shouldn't matter - you would still use the Master / Child relationship to control the sub form. As long as the query is updateable. If it isn't you may need to rationalise the query and do the sorting filtering on the subform not the query.

Robomato Over a year ago

If I try to set master or child on my sub form I get a "Can't build a link between unbound forms" error, I assumed that was because I'm using a query as my source object, is that correct?

Robomato Over a year ago

I'm pulling sorting information from an additional table (tbl_Task_Category: [Task Category], [Order]) which is why using a query is important.

Minty Over a year ago

You can set a link on any field / text box - just type the control name in, ignore the builder. You can use this technique to bind two subforms together on a unbound main form. See the link in my edit.

|

Robomato · Accepted Answer · 2017-03-24 15:43:16Z

0

Just to sum up the solution.

Link the Master and Child Fields to the sub form.
If you're getting a "Can't build a link between unbound forms" error, you can go into design view and manually type in the Parent/Child fields.

After that, the field will update automatically when entering a new row.

answered Mar 24, 2017 at 15:43

Robomato

2771 gold badge2 silver badges13 bronze badges

Collectives™ on Stack Overflow

MS-Access: Entering data via a query, how can I auto populate a column?

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related