Append Query when creating new record? (1 Viewer)

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
Afternoon all,

I have a table (TblEquipmentRequirements) with the following fields:

EquipmentRequirementID (PK)
EmployeeID (FK)
EquipmentID (FK)
Required (check box - YES /NO, not strictly necessary but required for UI/UX)

When I create a new record for a new employee, I'm trying to use an APPEND query to insert the list of available equipment (contained in TblEquipment) into the TblEquipmentRequirements - so staff can use the check boxes to select the required equipment.

Issue I'm having is I can't find a way to insert the new EmployeeID as well which is required - access returns the key validation error.

Any advice?

Thanks,
 

Ranman256

Well-known member
Local time
Today, 16:01
Joined
Apr 9, 2015
Messages
4,339
on the Employee form, when you add the new emp, then save,
the EmpID is created, so use this in the append queries to add the ID to the child tables too.

forms!fEmp!txtID
 

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
on the Employee form, when you add the new emp, then save,
the EmpID is created, so use this in the append queries to add the ID to the child tables too.

forms!fEmp!txtID

Thanks for the quick reply Ranman256 - any chance you could expand a bit further? I'm new to access and I have no background in IT...

As I understand it, you mean that the EmployeeID is generated and saved when I create a new employee, but how do I specify this, and wouldn't this mean I need to manually edit the append query for each new employee?

Thanks,
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:01
Joined
Aug 30, 2003
Messages
36,118
Ranman is suggesting you refer to the form containing the new ID in your append query:

forms!fEmp!txtID

You'd replace the form and control names with yours. I made them red for clarity.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:01
Joined
Feb 28, 2001
Messages
27,001
A question of clarification, please... You used this phrase "insert the list of available equipment" - which COULD mean that sometimes the equipment isn't there (i.e. not available at this moment). To help you do this better, we might need to know about your "available equipment" and how you KNOW that at the moment it is or isn't available when a new employee walks in the door.

For purity, the table you showed us (TblEquipmentRequirements) shouldn't necessarily have the Employee ID in it. I might have these tables.

First, a list of what you must issue for an employee. If this differs from employee to employee base on the job/role they hold, you need a field to indicate that the requirement is for employee type A or employee type B. And unless there would be a child table of this list, you don't need a separate ID field.

tblEquipRqmts: EmpType, EquipType, Required

Second, a list of what WAS issued for an employee. The employee type would be in the employee table.

tblEquipIssued: EmpID, EquipType, DateIssued, IssuedBy

Then you might be able to do something like this from the form driving this issuance:

Code:
INSERT INTO tblEquipIssued (EmpID, EquipType, DateIssued, IssuedBy) 
SELECT Me.EmpID, tblEquipRqmts.EquipType, Date(), Me.Issuer
FROM tblEmployee INNER JOIN tblEquipRqmts ON tblEmployee.EmpType = tblEquipRqmts.EmpType ;

The trick would then be to assure that you actually HAD the required equipment. There are ways to handle that but, not knowing your layout any better than this, it would be hard for me to guess the right way to handle it.

In this method, you have what might appear to be duplicate entries because of the repetition of commonly issued equipment for each different employee type. That is the nature of the beast when taking a very simple approach.

NOTE ALSO that this is "air code" intended for examples only, but this is one way to approach it.
 

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
Ranman is suggesting you refer to the form containing the new ID in your append query:



You'd replace the form and control names with yours. I made them red for clarity.
Again - thank you all for the replies. So - I think there's an issue somewhere which has had me banging my head against the wall. Every time I tried to run the query - after specifying the correct employee ID in the expression in the query, it pop up asking "enter parameter value" - even though it's all correct.

To make sure I wasn't going insane - I tried Ranman and pbaldys method in a new database file with a simple table, form and query and it works absolutely fine.

I've checked and triple checked the name of the form and the text box (it's built with the expression builder anyway) ... is there any reason why it would still come up with the enter parameter value box?
 

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
A question of clarification, please... You used this phrase "insert the list of available equipment" - which COULD mean that sometimes the equipment isn't there (i.e. not available at this moment). To help you do this better, we might need to know about your "available equipment" and how you KNOW that at the moment it is or isn't available when a new employee walks in the door.

For purity, the table you showed us (TblEquipmentRequirements) shouldn't necessarily have the Employee ID in it. I might have these tables.

First, a list of what you must issue for an employee. If this differs from employee to employee base on the job/role they hold, you need a field to indicate that the requirement is for employee type A or employee type B. And unless there would be a child table of this list, you don't need a separate ID field.

tblEquipRqmts: EmpType, EquipType, Required

Second, a list of what WAS issued for an employee. The employee type would be in the employee table.

tblEquipIssued: EmpID, EquipType, DateIssued, IssuedBy

Then you might be able to do something like this from the form driving this issuance:

Code:
INSERT INTO tblEquipIssued (EmpID, EquipType, DateIssued, IssuedBy)
SELECT Me.EmpID, tblEquipRqmts.EquipType, Date(), Me.Issuer
FROM tblEmployee INNER JOIN tblEquipRqmts ON tblEmployee.EmpType = tblEquipRqmts.EmpType ;

The trick would then be to assure that you actually HAD the required equipment. There are ways to handle that but, not knowing your layout any better than this, it would be hard for me to guess the right way to handle it.

In this method, you have what might appear to be duplicate entries because of the repetition of commonly issued equipment for each different employee type. That is the nature of the beast when taking a very simple approach.

NOTE ALSO that this is "air code" intended for examples only, but this is one way to approach it.
Thanks The_Doc_Man. It may sound peculiar but nearly every staff member has a different combination of requirements. If I created the EmpType - there'd nearly be as many as there were employees.

There's also no requirement to track the equipment.

I'm not sure it's great database design, but what I will end up with is:

EquipmentRequirementIDEmployeeIDEquipmentIDRequired
111Y
212Y
313Y
421Y
522N
623N
731N
832N
933N
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:01
Joined
Aug 30, 2003
Messages
36,118
is there any reason why it would still come up with the enter parameter value box?

The parameter prompt is Access telling you it can't find whatever is specified. Is the form open when the query runs? Is it perhaps a subform, in which case the syntax would be different?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:01
Joined
Feb 28, 2001
Messages
27,001
It may sound peculiar but nearly every staff member has a different combination of requirements.

Based on that statement, how would you ever know that the right person has the right equipment in the first place? I see your table that you called tblEquipmentRequirements, and that would tell you what a person needs. But there is still a disconnect in that design as to whether the person actually GOT the equipment or is still waiting because when (for example) Joe Schmuckatelli was hired, you were out of left-handed mustache cups.
You say that there is no need to track equipment, but there always is - if you still owe the person for the first issuance. Does that make sense?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:01
Joined
May 7, 2009
Messages
19,169
i was thinking that You Do not Need to Add all Equipments to your "Junction" table.
you add them as You Checked them. and Remove if Unchecked the equipment.
 

Attachments

  • Student and Equipments.accdb
    596 KB · Views: 452

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
The parameter prompt is Access telling you it can't find whatever is specified. Is the form open when the query runs? Is it perhaps a subform, in which case the syntax would be different?

I've triple checked - Syntax is definitely correct. It's strange - I created a new form based on the same table, and it works perfectly. Odd.
 

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
i was thinking that You Do not Need to Add all Equipments to your "Junction" table.
you add them as You Checked them. and Remove if Unchecked the equipment.
Arnelgp - thanks for your example. I'm probably missing something very obvious here, but in your example, when the record is changed on your employees form it resets the check boxes. They don't seem to be getting "added" anywhere?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:01
Joined
May 7, 2009
Messages
19,169
i have to recheck the code. it is working now.
 

Attachments

  • Student and Equipments.accdb
    552 KB · Views: 462

AndyC88

Member
Local time
Today, 20:01
Joined
Dec 4, 2020
Messages
44
That's absolutely perfect - had a look at the VB code and that's far beyond my capability. Hope you don't mind if I incorporate this?
 

Users who are viewing this thread

Top Bottom