Open Form to Specific Record

Happy Friday VBA coders,

I am aware that the thread title has been used elsewhere, however, after reading through them all am failing in an answer. The issue is as follows:

In my Access Database I have the following items:
1. A database with at least three columns: ID, WeekEnding, CustomerName.
2. A user form that displays the individual records of the database for each of these columns (i.e Columnar). Each record is cycled using forward and back buttons.
3. An entry form that contains a combo box and a Go button. The combo box lists the aforementioned user form.

I am trying to load the userform and go to a specific record - namely, the record containing the current weekending date. I want to ensure all other records are still available (i.e. Not conditional).

Please Login or Register  to view this content.

The CurrentWeekEndingDate is determined when the entry form is loaded (Global Variable)

The code does not produce an error. It opens the form but does not go to the required record.

I am quite familiar with Word and Excel VBA, but Access VBA is new to me. Can anybody please provide a working example?

Cheers

Jrnymn

Thank you for your response.

Unfortunately your code produces a "Method or Data member not found" error.

I tried using the following

Please Login or Register  to view this content.

The Microsoft Access database engine does not recognise "Jun" as a valid field name or expression.

Since the current weekending date is 15-Jun-13, that is a problem. Note that the field (or column) contains only dates.

Any further help would be appreciated.

Also, what was CmbListForms.Bookmark = rs.Bookmark meant to do?

Cheers

The CurrentWeekendingDate is a date. i.e 15-06-13. This variation is to make sure that the exact text of the field is returned.

Each record has a unique date (although it's not the primary key).

It is one of three columns in a table. It is to be used in a form such that when the form opens it automatically opens to the current weekending date.

The method not found error was returned at this line:

Please Login or Register  to view this content.

Thank you very much! Very happy.

I didn't get that wrapping it in # for dates was required, and as soon as I added it, it worked exactly as intended.

CurrentWeekEndingDate is determined by code at runtime. It's then compared against the predetermined list (table), and thanks to your assistance, displays the correct record when the form is opened.

The end result is:

Please Login or Register  to view this content.

Many thanks once again

Jrnymn