Insert record if No matching found

[midiman69]

I have a subform based on a lookup query.
The user enters a part number and it looks up the additional information.
If the user enters a part number that has no matching records is there a way I can automatically have this number inserted into the "newparts" table??

I have tried If Isnull Me.partno Then DoCmd.RunSQL Insert(tblnewparts), [partno]

This doesn't work and I really need to tie the new part to the main form record - Xfile+Issue.

Can any one help with this??

Cheers

Dave

 

[PHV]

Have a look at NotInList in the VBA help.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[midiman69]

Thanks PHV, but I am not using a combo box.
The part number is a field

Any other suggestions?

Thanks Dave

 

[idbr]

How about:

If DLookup("FormValue","PartsTable") = Null Then

Docmd.RunSQL "INSERT INTO PartsTable ( PartNo ) " & _
"SELECT " & FormValue & ";"

Or something along those lines...

HTH, Iain

 

[adalger]

Or possibly

DoCmd.GoToRecord , , acNewRec