Full Version: nit on list but no combo help
UtterAccess Forums > Microsoft® Access > Access Forms
hhh1027
I have form that I use for data entry and it is one to many relation when I enter item that is not on the main table I get error "you cannot add or change a record because a related record is required in table 'TblMain'". what I do then I close this and enter the product information in the main table and then return and enter the information. How can I make it when I enter item that is not in main table the entry will be erased and I get warning that says "you have to enter product information first" and it will open the form for product information "FrmProductInfo".
theDBguy
Hi,
Not sure if this will work but might be worth a shot. Try using the Form's OnError event to trap the error for a "no parent record" scenario. If successful, you can then discard the error, show your own message, and open the main form for the user to enter a new record first.
Hope that helps...
hhh1027
I did this in On error event of the form
DoCmd.OpenForm "FrmProductInof"
and it opens the form and I can add the new product and continue but I'm not sure how I can add the warning message
"This product is not in the list do you want to add" and the open the form. Or how I can add maybe dlookup in the beforeupdate event I'm not sure if this good idea.

Thanks
Edited by: hhh1027 on Sun Aug 16 0:32:52 EDT 2009.
polant
Assuming that you are using a combo with row source tblProducts to enter ProductID, you could use the NotInList event of the combo.

CODE
  
Private Sub cboProductID_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue ' to supress the standard Access msg
Msgbox "The product entered is not in the list!" vbInformation + vbOKOnly,"Data not found"
'If you want to undo just the combo, use:
Me.cboProductID.Undo
'If you want to undo the entire recond, use:
Me.Undo


NOTE: You need to set the combo's Limit To List property to Yes, in order for the NotInList event to fire.

Hope this helps
hhh1027
Polant,
I'm not using combo this gets enter by barcode reader.
polant
Ok, no prob. The title of the thread confused me.
hhh1027
I'm actually scanning the item using barcode reader and based on the barcode the rest of the fields gets updated, but one item is not in the main form I got the error. What I like to do is when this error happens get warning "This product is not in the list do you want to add" and undo the barcode entry and then open the form to add the product automatically. so far I did this in the form's error event.
oCmd.OpenForm "FrmProductInof"
This opens the form but I still need to add the warning message and Undo the barcode entry.
Thanks
theDBguy
Hi,
The Form's Error event will fire with certain application errors, you'll have to determine what the error number is for the situation you want to trap.
So, the first thing you'll need to do is remove the DoCmd.OpenForm code and replace it with:
MsgBox Err.Number
Once you know that number, you can then change the code to something like:
If Err.Number = ErrorNumberForNoParentRecordHere Then
Me.Undo
If MsgBox("That product is not in the list. Do you want to add it?", vbYesNo+vbQuestion, "No Record") = vbYes Then
DoCmd.OpenForm "FrmProductIof",,,,,acDialog
End If
End If
(untested)
Hope that helps...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.