Adding Records into two tables, I think...

[Steven Britton via AccessMonster.com]

I have two tables, one is tblParts and the other is tblSuppliers. I import
data into a temp table then append it to the tblPart if the Part Numbers
don't already exist. I append the part number and description which comes
over and use a zero 0 - as the supplier number. I have a form open that
selects zeros in the tblParts as the criteria. Then the users have to go
into the ancient system and look up the Supplier Numbers for these Parts.

If the supplier number already exists in the tblSuppliers (ie exsiting
vendors) I don't have a problem. But if there not in the tblsuppliers I
get an error saying they can't be found and the record can't be changed
because of the relantionship, which it correct...

How do I on the same form allow someone to change the supplier on the form
and if it's not listed in the tblSuppliers add a new supplier?

This is the SQL that controls the form.

SELECT tblParts.PartNum, tblParts.PartDesc, tblParts.PartSupplierId,
tblSupplier.SupplierNum, tblSupplier.SupplierName
FROM tblSupplier INNER JOIN tblParts ON tblSupplier.SupplierNum =
tblParts.PartSupplierId
WHERE (((tblParts.PartSupplierId)=0));


Thanks.

 

[SteveS]

I have two tables, one is tblParts and the other is tblSuppliers. I import
data into a temp table then append it to the tblPart if the Part Numbers
don't already exist. I append the part number and description which comes
over and use a zero 0 - as the supplier number. I have a form open that
selects zeros in the tblParts as the criteria. Then the users have to go
into the ancient system and look up the Supplier Numbers for these Parts.

If the supplier number already exists in the tblSuppliers (ie exsiting
vendors) I don't have a problem. But if there not in the tblsuppliers I
get an error saying they can't be found and the record can't be changed
because of the relantionship, which it correct...

How do I on the same form allow someone to change the supplier on the form
and if it's not listed in the tblSuppliers add a new supplier?

This is the SQL that controls the form.

SELECT tblParts.PartNum, tblParts.PartDesc, tblParts.PartSupplierId,
tblSupplier.SupplierNum, tblSupplier.SupplierName
FROM tblSupplier INNER JOIN tblParts ON tblSupplier.SupplierNum =
tblParts.PartSupplierId
WHERE (((tblParts.PartSupplierId)=0));


Thanks.

It sounds like you are getting the error because there is not a related
record in the table tblSuppliers that matches a supplier number of 0.

Try adding a record in tblSuppliers where

supplier number = 0
supplier name = "?????" (or "Unknown" or "--------" or ...)

 

[Ken Snell [MVP]]

Provide a command button for the user to click that will open a form based
on tblSuppliers. Allow the user to enter a new supplier. When the form
closes, requery the combo box that you are using for the person to select
the supplier (I assume that you are doing this so that the user cannot enter
an invalid/nonexistent supplier) so that the newly added supplier can be
selected.

 

[Steven M. Britton]

Ken,

Thanks - I was trying to go with the NotInList and use a vbYesNo to have
them open the form. However if they key in a number that isn't listed and
get an error message because it can't add do to the relationship. This is
the error:

The Microsoft Jet database engine cannot find a record in teh table
'tblSupplier' with key matching field(s) 'PartSupplierId'.

Where did I go wrong?

This is also the code from the NotInList:

Private Sub cmbSupplierNum_NotInList(NewData As String, Response As Integer)
Dim strTitle As String
Dim strMsg As String
Dim intStyle As Integer

strMsg = "The Supplier Number you entered is not currenting" _
& " in the LifeCycle Database. Would you like to add them now?"
strTitle = "Supplier Missing"
intStyle = vbYesNo

If (MsgBox(strMsg, intStyle, strTitle)) = vbYes Then
DoCmd.OpenForm "frmAddSupplier"
Else: Exit Sub
End If

 

[Ken Snell [MVP]]

Open the frmAddSupplier in dialog mode. Then have that form do the new entry
of the suppplier. Then use the Response variable to turn off the error
message.


If (MsgBox(strMsg, intStyle, strTitle)) = vbYes Then
DoCmd.OpenForm "frmAddSupplier", , , , , acDialog
Response = acDataErrAdded
Else: Exit Sub
End If

 

[Steven M. Britton]

I'll have it open in the dialog mode which is fine. However it doesn't open
because the code never executes. I get the same error message when I try to
add a suppiler that isn't listed in the cmb box. The PartSupplerID is in the
tblParts and is the key to the tblSupplier. I know is doesn't exist and
would like the form to open so that can be properly added?

The Microsoft Jet database engine cannot find a record in the table

 

[Ken Snell [MVP]]

What code are you running on other events for the combo box and/or the form?

 

[Steven M. Britton]

I'm not running any other code on the form or combo box.

This is the SQL of the form:

SELECT tblParts.PartNum, tblParts.PartDesc, tblParts.PartSupplierId,
tblSupplier.SupplierNum, tblSupplier.SupplierName
FROM tblSupplier INNER JOIN tblParts ON tblSupplier.SupplierNum =
tblParts.PartSupplierId
WHERE (((tblParts.PartSupplierId)=0));

And this is what I have for the Combo Box:

SELECT tblSupplier.SupplierNum, tblSupplier.SupplierName
FROM tblSupplier;

And the Control Source is PartSupplierId

 

[Ken Snell [MVP]]

Do you have the Limit To List property set to Yes for the combo box? If you
don't, then the Not In List event will not occur, and your code will not
run.
--

Ken Snell
<MS ACCESS MVP>

 

[Steven M. Britton]

DOH, thanks Ken, didn't check that first off. My mistake...

 

[Ken Snell [MVP]]

DOH, thanks Ken, didn't check that first off. My mistake...

< g > I should have thought of it earlier in the thread, too.