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(dbOpenDy
naset)
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()
'Declarations Here
Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim rs2 As Recordset
Dim NewRec As Variant
Dim varIsApproved As Variant
'Settings Here
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(dbOpenDy
naset) '<----- PRODUCES ERROR
DoCmd.Close acForm, "Popup1"
'Validation here
If rs.RecordCount > 0 Then
GoTo Cont1:
Else
MsgBox "That Art Log Number does not exist in this database yet. You will have to start with a blank Request.", vbInformation, "Title Here"
GoTo AddNewFields
End If
Cont1:
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
DoCmd.OpenForm "Form1"
Set rs2 = Forms![Form1].RecordsetClo
ne
'open Form and copy some fields from original recordset to the new record
rs2.AddNew
rs2![Manual] = rs![Manual]
rs2![Chapter] = rs![Chapter]
rs2![Section] = rs![Section]
'there are many other fields to go here
Else
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
End If
AddNewFields:
[Forms]![Form1]![Author] = MyCurrentUser()
[Forms]![Form1]![Extension
] = MyPhone()
[Forms]![Form1]![Art Number] = "0"
DoCmd.Echo True
Set rs = Nothing
Set db = Nothing
Set rs2 = Nothing
End1:
End Sub
