adding items to a form & table from a query

[robojeff]

There is probably a better way of doing this and that is why I am posting my code on here...

I have a form (part_frm) which is tied to a table (PartMade_tbl) which includes a part number (part), a description (desc), a revision level (rev), a user's name, and today's date.

The fields on the form in which the user completes include:
part - part number
desc - part description
qty - quantity of parts
operator - user name as selected by drop down list box
rev - revision of part

I have a query (desc_rev) which creates a table (partentry_tbl) of one item which includes the part number (IPROD), the part Description (IDESC), and the Part revision (IFENO)

I want the operator to open the form (and today's date should populate the date field on the form) and they should enter their name, the part number and a quantity and then the query (desc_rev) should look up the part description and revision level which I then attempt to set a couple of the form's fields and then attempt to store all inforamtion on the form to a record. With the code below, I get a 2046 run time error stating that the saverecord is not currently available.

Is there a better/cleaner way of doing this?

' query - desc_rev
SELECT ONEBPCSF_IIM.IPROD, ONEBPCSF_IIM.IDESC, ONEBPCSF_IIM.IFENO, Forms!part_frm!date AS [DAY], Forms!part_frm!operator AS [User], Forms!part_frm!Qty AS Qty
FROM ONEBPCSF_IIM
WHERE (((ONEBPCSF_IIM.IPROD)=[Forms]![part_frm]![pnum]));

' my code for adding a record
Dim dbs As DAO.Database
Dim db As DAO.Database
Dim rstProduct As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT partentry_tbl.IPROD, partentry_tbl.IDESC, partentry_tbl.IFENO FROM partentry_tbl; "

Set rstProduct = dbs.OpenRecordset(strSQL, dbOpenDynaset)

If rstProduct.EOF = False Or rstProduct.BOF = False Then
    rstProduct.MoveFirst    ' Get first record in Table

    Me.Rev = rstProduct!IFENO
    Me.Desc = rstProduct!IDESC
End If

rstProduct.Close    ' Close the recordset.
Set rs = Nothing
Set db = Nothing
RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec

 

[MazeWorX]

Why not use the Dlookup function to look up the rev and descriptions based on the users input and then save

'control source for the description
=DLookup("[IDESC]", "ONEBPCSF_IIM","[IPROD]"=Forms![part_frm]!pnum)

'control source for the rev
=DLookup("[IFENO]", "ONEBPCSF_IIM","[IPROD]"=Forms![part_frm]!pnum)

untested

HTH << MaZeWorX >> Remember amateurs built the ark - professionals built the Titanic [flush]