Open form to a specific record

[Rick]

I have a form based on an Assignment which contains the
following fields:

WeekID autonumber PK
BeginWeek (short date format)
EndWeek (short date format)

I want to open the form to display the week range that
matches the current date. For example:

Assignment Table
WeekID BeginWeek EndWeek
45 09/26/04 10/02/04
46 10/03/04 10/09/04
47 10/10/04 10/16/04
48 10/17/04 10/23/04
49 10/24/04 10/30/04

Assuming today's date is 10/14/04 I want the form to open
with the record which relates to the week of 10/10/04 to
10/16/04. Presently it opens for the first record in the
assignment table and the user must use a combo box to
specify the current week that they wish to work with.

Not sure how to use the Find or Seek actions in Access or
the syntax to accomplish this.

TIA

 

[G. Vaught]

Create a query, using all the fields from the approprite table and link this
query to your form.
Within the query type in the criteria section the following
Like [Enter Begin Date] & "*" under the BeginWeek field. Under the EndWeek
field type Like[Enter End Date] & "*". Anytime you open the form the user
will be prompted for this information and should be taken to the appropriate
record(s).

What the asterisk does is allow the user type a portion of the dates, such
as 10/10 rather than specifically 10/10/2004. Also the asterisk allows the
user to press enter, not entering any data to the prompt on both prompts to
get all the records.

 

[Guest]

Thanks for the suggestion. However; not quite what I had in mind. I want
the program to read today's date from the system [i.e. Date()] find the
correct week that the date belongs to and open the form to display this week
range.

Create a query, using all the fields from the approprite table and link this
query to your form.
Within the query type in the criteria section the following
Like [Enter Begin Date] & "*" under the BeginWeek field. Under the EndWeek
field type Like[Enter End Date] & "*". Anytime you open the form the user
will be prompted for this information and should be taken to the appropriate
record(s).

What the asterisk does is allow the user type a portion of the dates, such
as 10/10 rather than specifically 10/10/2004. Also the asterisk allows the
user to press enter, not entering any data to the prompt on both prompts to
get all the records.

I have a form based on an Assignment which contains the
following fields:

WeekID autonumber PK
BeginWeek (short date format)
EndWeek (short date format)

I want to open the form to display the week range that
matches the current date. For example:

Assignment Table
WeekID BeginWeek EndWeek
45 09/26/04 10/02/04
46 10/03/04 10/09/04
47 10/10/04 10/16/04
48 10/17/04 10/23/04
49 10/24/04 10/30/04

Assuming today's date is 10/14/04 I want the form to open
with the record which relates to the week of 10/10/04 to
10/16/04. Presently it opens for the first record in the
assignment table and the user must use a combo box to
specify the current week that they wish to work with.

Not sure how to use the Find or Seek actions in Access or
the syntax to accomplish this.

TIA

 

[John Vinson]

Thanks for the suggestion. However; not quite what I had in mind. I want
the program to read today's date from the system [i.e. Date()] find the
correct week that the date belongs to and open the form to display this week
range.

Base the Form, not on the Table, but on a Query with critera:

[BeginWeek] <= Date() AND [EndWeek] >= Date()

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Sorry, I guess I am not making myself clear. After attempting several
approaches I believe I have found the method necessary to accomplish what I
am attempting to do. However; I am not sure of the code to complete the
task.

It would seem that I should be using the OpenArgus property and then the
FindFirst method to open the form to a specific record.

For example:

If the system date on the computer if 10/18/04 the form should open and
display the record:

WeekId BeginWeek EndWeek
48 10/17/04 10/23/04

Can anyone suggest the code to accomplish this?

Thanks for the suggestion. However; not quite what I had in mind. I want
the program to read today's date from the system [i.e. Date()] find the
correct week that the date belongs to and open the form to display this week
range.

Base the Form, not on the Table, but on a Query with critera:

[BeginWeek] <= Date() AND [EndWeek] >= Date()

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Sorry, I guess I am not making myself clear. After attempting several
approaches I believe I have found the method necessary to accomplish what I
am attempting to do. However; I am not sure of the code to complete the
task.

It would seem that I should be using the OpenArgus property and then the
FindFirst method to open the form to a specific record.

For example:

If the system date on the computer if 10/18/04 the form should open and
display the record:

WeekId BeginWeek EndWeek
48 10/17/04 10/23/04

Can anyone suggest the code to accomplish this?

My suggestion will work. Did you try it?

If you prefer to base the Form on the table (which is NOT necesary),
you can use the OpenForm method's WhereCondition field:

DoCmd.OpenForm "formname", WhereCondition := _
"[BeginWeek] <= #" & Date() & "# AND [EndWeek] >= #" & Date() & "#"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Both of John's suggestions will work.

Good work John.

Sorry, I guess I am not making myself clear. After attempting several
approaches I believe I have found the method necessary to accomplish what I
am attempting to do. However; I am not sure of the code to complete the
task.

It would seem that I should be using the OpenArgus property and then the
FindFirst method to open the form to a specific record.

For example:

If the system date on the computer if 10/18/04 the form should open and
display the record:

WeekId BeginWeek EndWeek
48 10/17/04 10/23/04

Can anyone suggest the code to accomplish this?

My suggestion will work. Did you try it?

If you prefer to base the Form on the table (which is NOT necesary),
you can use the OpenForm method's WhereCondition field:

DoCmd.OpenForm "formname", WhereCondition := _
"[BeginWeek] <= #" & Date() & "# AND [EndWeek] >= #" & Date() & "#"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John. This code does open the form to the week I want the user to see
initially. However; it also restricts the user to viewing this week only.
All suggestions made thus far apprear to be using a filter approach to
produce only one record.

How can I have Access permit the user to view initially the record your code
produces initially and allow them to scroll forward or backward to view other
records thereafter?

Sorry, I guess I am not making myself clear. After attempting several
approaches I believe I have found the method necessary to accomplish what I
am attempting to do. However; I am not sure of the code to complete the
task.

It would seem that I should be using the OpenArgus property and then the
FindFirst method to open the form to a specific record.

For example:

If the system date on the computer if 10/18/04 the form should open and
display the record:

WeekId BeginWeek EndWeek
48 10/17/04 10/23/04

Can anyone suggest the code to accomplish this?

My suggestion will work. Did you try it?

If you prefer to base the Form on the table (which is NOT necesary),
you can use the OpenForm method's WhereCondition field:

DoCmd.OpenForm "formname", WhereCondition := _
"[BeginWeek] <= #" & Date() & "# AND [EndWeek] >= #" & Date() & "#"


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Thanks John. This code does open the form to the week I want the user to see
initially. However; it also restricts the user to viewing this week only.
All suggestions made thus far apprear to be using a filter approach to
produce only one record.

How can I have Access permit the user to view initially the record your code
produces initially and allow them to scroll forward or backward to view other
records thereafter?

Thank you for the clarification - I wasn't reading your original post
that way!

I'd suggest that you don't need to pass anything from the calling form
if today's date is always the desired target. Instead, put navigation
code in the Form's Open event:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[BeginWeek] <= #" & Date & "# AND [EndWeek] >= #" _
& Date & "#"
If rs.NoMatch Then
MsgBox "No data for this week!"
rs.MoveFirst
End If
Me.Bookmark = rs.Bookmark
Set rs = Nothing


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Excellent ! John. Thank you for your help and especially your patience.

Thanks John. This code does open the form to the week I want the user to see
initially. However; it also restricts the user to viewing this week only.
All suggestions made thus far apprear to be using a filter approach to
produce only one record.

How can I have Access permit the user to view initially the record your code
produces initially and allow them to scroll forward or backward to view other
records thereafter?

Thank you for the clarification - I wasn't reading your original post
that way!

I'd suggest that you don't need to pass anything from the calling form
if today's date is always the desired target. Instead, put navigation
code in the Form's Open event:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[BeginWeek] <= #" & Date & "# AND [EndWeek] >= #" _
& Date & "#"
If rs.NoMatch Then
MsgBox "No data for this week!"
rs.MoveFirst
End If
Me.Bookmark = rs.Bookmark
Set rs = Nothing


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps