What I do with similar things is use a form that's not
bound to any data source. When it opens or goes to a new
record, set your field defaults. Enter your data, print
and then append the data to your table.
When the form opens, use a DMax function to get the last
check number written. Add 1 to it and populate the check
numebr field.
Check the Help files for details on DMax but here's code
that should work for you. Assume your form has a TextBox
(txtChkNumber) and your main table is tblChecks, with the
field ChkNumber.
Dim i as Integer
(or Dim l as Long if your check numbers are over 32000)
i = DMax("[ChkNumber]","tblChecks")
[txtChkNumber] = (i + 1)
The only downside to updating from an unbound form is that
the Query Designer won't let you build a query without at
least one source. So I do it in code - build a SQL string
to append the data and then execute it. It's pretty
simple - assume your form also has TextBoxes for the payee
(txtPayTo), amount (txtAmount) and note (txtNote) and that
there are corresponding fields in the tblChecks table and
that your form also has a CommandButton(cmdUpdate) for
running the code:
Private Sub cmdUpdate_Click()
' declare variables
Dim intChkNumber as Integer
Dim strPayTo as String
Dim curAmount as Currency
Dim strNote as String
Dim strI as String
Dim strS as String
Dim strSQL as String
' set variables
intChkNumber = [txtChkNumber]
strPayTo = [txtPayTo]
curAmount = [txtAmount]
strNote = [txtNote]
' build SQL string
strI = "INSERT INTO tblChecks ( ChkNumber, ChkDate, PayTo,
Amount, Note ) "
strS = "SELECT " & intChkNumber & ",#" & Date() & "#,'" &
strPayTo & "'," & curAmount & ",'" & strNote & "'"
strSQL = strI & strS & ";"
' run sql string
Docmd.RunSQL strSQL
End Sub
Then all you need to do is reset your form fields and
you're fine.
Hope this helps!
Howard Brody
-----Original Message-----
I created a handcheck table that I pull recurring fields from to create the
check. I then date and (hopefully automatically) number and then print them
.. Once confirmed OK I would use an append query to write them out to a
permanent table and clear the handcheck tables date and check number fields
for next time.
.
