Purpose of code: See if an existing record is completed before ANOTHER record can be created for the same "Log Number".
Here is what I am trying to do:
1) Open a recordset from a stored query.
2) If recordset finds a record, continue. If recordset is empty, display msgbox. QUIT.
3) If the SQL returns data, it is the record I need at the top (it's already sorted). If a field [Date Poscripted] in that record is not Null, continue, else display msgbox "Existing record not completed..."
4) If record does exist AND is completed, then copy some fields to the new record.
That's it. I imagine this general process can not be too uncommon. Am I approaching this properly? Is there a better way? Perhaps SQL embedded in the code (no need for a stored query?) OR is there a way to copy all the fields from the old record to the new record, THEN 'Null' the fields I need to? This would eliminate the need for all fields to be copied to be listed in the code itself.
The code(s) below partially work and are a basic outline. At present, this line:
Set rs = qdf.OpenRecordset(dbOpenDynaset)
produces this error:
"Too few parameters. Expected 2."
Your thoughts - or what can I do to get this existing code (see below) working? Mega thanks!
Stored Query (SQL):
PARAMETERS [Enter the Art Log Number] Text;
SELECT TOP 1 [Main Table].*
FROM [Main Table]
WHERE ((([Main Table].[Art Number])=IIf(Left([Enter the Art Log Number],1)="a",Mid([Enter the Art Log
Number],2),[Enter the Art Log Number])))
ORDER BY [Main Table].[Date Submitted To Art Dept] DESC;
The VB Code under a command button:
Private Sub Revise_Click()
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
Set db = CurrentDb
Set qdf = db.QueryDefs("qryArtNumber")
qdf.Parameters("[Enter the Art Log Number]") = InputBox("Enter the Art Log Number", , "Value Required")
'------------------------------WORKS TO HERE
Set rs = qdf.OpenRecordset(dbOpenDynaset) '<----- PRODUCES ERROR
DoCmd.Close acForm, "Popup1"
If rs.RecordCount > 0 Then
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
varIsApproved = DLookup("[Date Poscripted]", "[Main Table]", "[Art Number]= " & NewRec) 'Validation if Art Log is signed off
If (Nz(varIsApproved, "") <> "") Then 'If Writer Approval date is not equal to null then
Set rs2 = Forms![Form1].RecordsetClone
'open Form and copy some fields from original recordset to the new record
rs2![Manual] = rs![Manual]
rs2![Chapter] = rs![Chapter]
rs2![Section] = rs![Section]
'there are many other fields to go here
MsgBox "This Art Log Number is currently being worked. Please see the bla bla Department for assistance.", vbInformation, "Title Here"
DoCmd.OpenForm "Form1", acNormal, , , acFormAdd
[Forms]![Form1]![Author] = MyCurrentUser()
[Forms]![Form1]![Extension] = MyPhone()
[Forms]![Form1]![Art Number] = "0"
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing