You cannot add or change a record because a related record is required in table tbl_Location

  • Thread starter Thread starter Little Penny
  • Start date Start date
L

Little Penny

I have an inventory database, which keeps track of computer related equipment. I created a location table, which has a relationship of one to many on
several tables (tbl_Computer, tbl_Monitors, tbl_Printers, tbl_Peripherals). I have created two forms one to inventory new computer as they are
received and one to link to location when it's installed. On my new install form I have basic info to about the PC which are locked. And a location ID
field where info must be enter. There are several hundred locations. Some of which are not all in the location table. So when I enter a location into
the form the is no in the location table I get "You cannot add or change a record because a related record is required in table tbl_Location"

If a location is not in the locations table is there a way for access to ask the user if they want to add the new location ID to this table via this
form.
 
I have an inventory database, which keeps track of computer related equipment. I created a location table, which has a relationship of one to many on
several tables (tbl_Computer, tbl_Monitors, tbl_Printers, tbl_Peripherals). I have created two forms one to inventory new computer as they are
received and one to link to location when it's installed. On my new install form I have basic info to about the PC which are locked. And a location ID
field where info must be enter. There are several hundred locations. Some of which are not all in the location table. So when I enter a location into
the form the is no in the location table I get "You cannot add or change a record because a related record is required in table tbl_Location"

If a location is not in the locations table is there a way for access to ask the user if they want to add the new location ID to this table via this
form.
Click to expand...

Happy New Year Penny,

You could trap Error 3201 in the Form's OnError event and allow the user to add
it. I think it would be smoother and more intuitive if you did a DLookup() or
DCount() in the AfterUpdate event of the TextBox. You could also switch to a
ComboBox and use the NotInList event to add it.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
One additional question. As I am testing this out, I notice that if I change a location and it not in list and then choose not to add to list the
original value disappears. Can have it so if I choose not to add to list the original field value returns.

Here is my VB code



Private Sub cboLocationID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboLocationID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Loaction ID " & Chr(34) & NewData & _
Chr(34) & " is not currently in loaction list." & vbCrLf & _
"Would you like to add the new LoactionID to the list now?" _
, vbQuestion + vbYesNo, "Location ID")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_Location([LocationID]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "LocationID"
Response = acDataErrContinue
End If
cboLocationID_NotInList_Exit:
Exit Sub
cboLocationID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboLocationID_NotInList_Exit
End Sub
 
Answered in line:

One additional question. As I am testing this out, I notice that if I change a location and it not in list and then choose not to add to list the
original value disappears. Can have it so if I choose not to add to list the original field value returns.

Here is my VB code



Private Sub cboLocationID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboLocationID_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Loaction ID " & Chr(34) & NewData & _
Chr(34) & " is not currently in loaction list." & vbCrLf & _
"Would you like to add the new LoactionID to the list now?" _
, vbQuestion + vbYesNo, "Location ID")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tbl_Location([LocationID]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new job title has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "LocationID"
Response = acDataErrContinue
Click to expand...

Me.cboLocationID.UnDo '-- Add this line
End If
cboLocationID_NotInList_Exit:
Exit Sub
cboLocationID_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume cboLocationID_NotInList_Exit
End Sub
Click to expand...
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
I cannot thank you enough for you help. I do have another question. Maybe you can give me some direction. I have seven tables (Computers Laptop
Printer Peripherals Monitors and Location. The locations table has a one to many relationship with the (Computers Printers Peripherals and Monitors)
table. Laptops do not because we are more concern about user info. Computer most of the time have more than one user (Different Shifts).
My Users table has a one to many relationship with the (Computers Laptop and Peripherals)

The User table consist of several fields

Table: tbl_UserInfo

Name Type Size
UserID (PK) Long Integer 4
User_FName Text 50
User_LName Text 50
User_PhoneExt Text 50
User_CellPhone Text 50
User_Pager Text 50
User_Email Text 50
LocationID Long Integer 4 (Not used yet)
ComputerID Long Integer 4

The problem is when I use a form to link a Laptop, Computer or, Peripheral to a user I need to know the if the user already have and ID number if so
what it is. Is there a way to handle this differently? For instance since names are easier for people to deal with is there a way to type in the users
name and it retrieve the UserID and if they don't have one add the users required info and generate a user ID.


Thanks
 
I cannot thank you enough for you help. I do have another question. Maybe you can give me some direction. I have seven tables (Computers Laptop
Printer Peripherals Monitors and Location. The locations table has a one to many relationship with the (Computers Printers Peripherals and Monitors)
table. Laptops do not because we are more concern about user info. Computer most of the time have more than one user (Different Shifts).
My Users table has a one to many relationship with the (Computers Laptop and Peripherals)

The User table consist of several fields

Table: tbl_UserInfo

Name Type Size
UserID (PK) Long Integer 4
User_FName Text 50
User_LName Text 50
User_PhoneExt Text 50
User_CellPhone Text 50
User_Pager Text 50
User_Email Text 50
LocationID Long Integer 4 (Not used yet)
ComputerID Long Integer 4

The problem is when I use a form to link a Laptop, Computer or, Peripheral to a user I need to know the if the user already have and ID number if so
what it is. Is there a way to handle this differently? For instance since names are easier for people to deal with is there a way to type in the users
name and it retrieve the UserID and if they don't have one add the users required info and generate a user ID.


Thanks
Click to expand...

What you are describing is the same code you published earlier in this thread
for a NotInList event for a ComboBox. If the UserID is an AutoNumber (which it
should be) then it will work. I'm surprised there is a ComputerID field in the
User table. I don't think it is necessary to accomplish what you are doing.
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.
 
Back
Top