form question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a form that 3 different users will enter data into. one user will
fill in one field on this form, another user will fill in 2 fields of the
form, and the third user will fill in the rest. i have this form pop for a
certain date and they all enter their data on that given date. now we are
changing it for a month and not a certain date. for example: when the users
open the form i would like them to see data entered for march 06 and then
when then month gets here on april 1 it will default to april 06 is there a
way to do this
 
Maybe I don't fully understand the question, but if what you are saying is
"how do I find the 6th of a month, then it would be lilke this

dtmSixthDay=dateserial(year(date),month(date),6)
 
right now i have the form opening to the current date, should i have two
separate fields one for the month and one for year? I'm not sure how i
should use the dlookup ? do i use that in the open form even? im not sure
how to get this work?
 
Okay, gottcha. First, is there a field in the table that tells us which
month and year a record is for? If there is, then do a DLookup on that table
looking for a record with the month and year of the current date. If it is
found, open the form with that record as the current record. If there is
not, open the form to a new recors.
 
i think i confused you sorry, these three users will fill the form out on
different dates throughout the month. i want the users to be able to go into
that form and fill out their parts at anytime during the month of march in
that one record and then the next month have it open up to a new record for
april for example
month no user1 no user 2 no user3
3/2006 1 3 5

everytime it opens they get the same form unitl april would hit
 
Use the form's Load event.
Two fields would be good, one for year and one for month, both as Long Integer
After I looked at it a little more, I decided the coding would be easier if
we use the RecordsetClone and Bookmark way of doing it. In the code below,
we get the month and year of the current date. We then use the
recordsetclone of the form and do a FindFirst, which will position us on the
correct record if it exists. If it does not exist, the NoMatch property will
be true, so we create a new record. If it does exist, we make the form's
bookmark the same as the clone's book mark and we are on the record we want.

Dim rst as Recordset
Dim lngCurrYear as Long
Dim lngCurrMonth as Long

lngCurrYear = Year(Date)
lngCurrMonth = Month(Date)

Set rst = Me.RecordsetClone
With rst
.FindFirst ""[TheMonthField]", "MyTableName", _
"[TheMonthField] = " & lngCurrMonth & " And [TheYearField] = " _
& lngCurrYear"
If .NoMatch Then 'It does not exist, go to a new record
DoCmd.GotoRecord acNewRec
Else
Me.Bookmark = .Bookmark
End If
End With

Set rst = nothing
Else
 
Back
Top