Help with code

[Guest]

Hi

I need help with some code. What I want to do is, when a command button is
clicked on a form I want it to look for a record with today’s date and open
that record in another form, if there is no record with today’s date I want
it to create one and open it in another form

As always any help is greatly appreciated.

Thanks
Chuck

 

[Guest]

I would do it a little differently. It will make the coding easier.
What I would do is find the date in the table, if it is there and pass the
date to the form using the OpenArgs argument of the OpenForm method. If The
date is not in the table, I would pass a Null to the form. Then in the
form's (the form you are opening) Load event, check the form's OpenArgs
property and navigate to the record if there is a date or create a new record
if there is no date:

Code for the Command Button:

Private Sub OpenForm_Click()
Dim varOpenArg As Variant

varOpenArg = DLookup("[TheDateField], "YourTableName", "[TheDateField] =
#" & Date() & "#"
If Not IsNull(varOpenArg) Then
varOpenArg = CStr(varOpenARg)
End If
DoCmd.OpenForm "TheOtherFormName", , , , , , varOpenArg
End Sub

Code for the other form's Load Event:

If IsNull(Me.OpenARgs) Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
With Me.RecordsetClone
.FindFirst "[TheDateField] = #" & CDate(Me.OpenArgs) & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End IF
End With
End If

 

[Guest]

Dave

I’m sorry I think I worded my original question wrong. It’s the same form
I want to open whether or not a record with today’s date exists, but if
there is a record with today’s date I want to open that if not then I want to
create a record with today’s date and open it.

Thanks in advance for your help I hope I didn’t cause too much trouble for
you with my original wording.

Chuck

I would do it a little differently. It will make the coding easier.
What I would do is find the date in the table, if it is there and pass the
date to the form using the OpenArgs argument of the OpenForm method. If The
date is not in the table, I would pass a Null to the form. Then in the
form's (the form you are opening) Load event, check the form's OpenArgs
property and navigate to the record if there is a date or create a new record
if there is no date:

Code for the Command Button:

Private Sub OpenForm_Click()
Dim varOpenArg As Variant

varOpenArg = DLookup("[TheDateField], "YourTableName", "[TheDateField] =
#" & Date() & "#"
If Not IsNull(varOpenArg) Then
varOpenArg = CStr(varOpenARg)
End If
DoCmd.OpenForm "TheOtherFormName", , , , , , varOpenArg
End Sub

Code for the other form's Load Event:

If IsNull(Me.OpenARgs) Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
With Me.RecordsetClone
.FindFirst "[TheDateField] = #" & CDate(Me.OpenArgs) & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End IF
End With
End If
--
Dave Hargis, Microsoft Access MVP

Hi

I need help with some code. What I want to do is, when a command button is
clicked on a form I want it to look for a record with today’s date and open
that record in another form, if there is no record with today’s date I want
it to create one and open it in another form

As always any help is greatly appreciated.

Thanks
Chuck

 

[Guest]

What you are discribing is exactly what the code is doing. It opens a form
named "TheOtherForm" regardless of whether a record with today's date exists
in the table or not.
--
Dave Hargis, Microsoft Access MVP

Dave

I’m sorry I think I worded my original question wrong. It’s the same form
I want to open whether or not a record with today’s date exists, but if
there is a record with today’s date I want to open that if not then I want to
create a record with today’s date and open it.

Thanks in advance for your help I hope I didn’t cause too much trouble for
you with my original wording.

Chuck

I would do it a little differently. It will make the coding easier.
What I would do is find the date in the table, if it is there and pass the
date to the form using the OpenArgs argument of the OpenForm method. If The
date is not in the table, I would pass a Null to the form. Then in the
form's (the form you are opening) Load event, check the form's OpenArgs
property and navigate to the record if there is a date or create a new record
if there is no date:

Code for the Command Button:

Private Sub OpenForm_Click()
Dim varOpenArg As Variant

varOpenArg = DLookup("[TheDateField], "YourTableName", "[TheDateField] =
#" & Date() & "#"
If Not IsNull(varOpenArg) Then
varOpenArg = CStr(varOpenARg)
End If
DoCmd.OpenForm "TheOtherFormName", , , , , , varOpenArg
End Sub

Code for the other form's Load Event:

If IsNull(Me.OpenARgs) Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
With Me.RecordsetClone
.FindFirst "[TheDateField] = #" & CDate(Me.OpenArgs) & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End IF
End With
End If
--
Dave Hargis, Microsoft Access MVP

Hi

I need help with some code. What I want to do is, when a command button is
clicked on a form I want it to look for a record with today’s date and open
that record in another form, if there is no record with today’s date I want
it to create one and open it in another form

As always any help is greatly appreciated.

Thanks
Chuck

 

[Guest]

Got it , I'm a little slow when it comes to code

Thank you

What you are discribing is exactly what the code is doing. It opens a form
named "TheOtherForm" regardless of whether a record with today's date exists
in the table or not.
--
Dave Hargis, Microsoft Access MVP

Dave

I’m sorry I think I worded my original question wrong. It’s the same form
I want to open whether or not a record with today’s date exists, but if
there is a record with today’s date I want to open that if not then I want to
create a record with today’s date and open it.

Thanks in advance for your help I hope I didn’t cause too much trouble for
you with my original wording.

Chuck

I would do it a little differently. It will make the coding easier.
What I would do is find the date in the table, if it is there and pass the
date to the form using the OpenArgs argument of the OpenForm method. If The
date is not in the table, I would pass a Null to the form. Then in the
form's (the form you are opening) Load event, check the form's OpenArgs
property and navigate to the record if there is a date or create a new record
if there is no date:

Code for the Command Button:

Private Sub OpenForm_Click()
Dim varOpenArg As Variant

varOpenArg = DLookup("[TheDateField], "YourTableName", "[TheDateField] =
#" & Date() & "#"
If Not IsNull(varOpenArg) Then
varOpenArg = CStr(varOpenARg)
End If
DoCmd.OpenForm "TheOtherFormName", , , , , , varOpenArg
End Sub

Code for the other form's Load Event:

If IsNull(Me.OpenARgs) Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
With Me.RecordsetClone
.FindFirst "[TheDateField] = #" & CDate(Me.OpenArgs) & "#"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End IF
End With
End If
--
Dave Hargis, Microsoft Access MVP


:

Hi

I need help with some code. What I want to do is, when a command button is
clicked on a form I want it to look for a record with today’s date and open
that record in another form, if there is no record with today’s date I want
it to create one and open it in another form

As always any help is greatly appreciated.

Thanks
Chuck