open an access form to a specific record

[Trixxnixon]

First I will give you a high level description of what I am trying to accomplish.

As I said before I have 2 tables, the main table whos records are distinguished by a tracking number
And a pending record table that serves the purpose of creating pending records for the records in the main table(this also has a primary key called “recordno”).

The records from the main table are made through forms the client uses, and processed through forms the specialist uses, which open up the information entered by the client plus additional fields the specialists need.

Sometimes these records need to be pended for approval. I am trying to automate this part of the application.

I have created a form (frmpend). This form currently transfers the tracking number from the specialist form, and opens the pend form with the tracking number field populated.

It also contains a “start pend date”, “end pend date” field, along with a status check box. All of these are controlled by one button. When the start date is populated, the check box( checked when file is pended )is also populated. When the end date is populated, the check box is cleared.

What I want this form to do, is search for any records that are currently marked as pended, and open them up with the form for editing. If no records are found, a new record will be created.

It should only be looking for records based on the tracking number carried over from the previous form, as well as a status marked as yes.

If I am able to set it up this way, only one record per tracking number can be marked as “yes” at one time.

I do have a query that searches for the tracking number and a status if “yes”. I was also able to use the dlookup to retrieve the first “recordno” of a pending record marked “yes” for a given tracking number.


Here are the tables and forms info,

tables
tblMain: Tracking Number,(there is quite a bit of other information on this table, but none of it is necessary for this part of the application.) tblPend: Recordno, start pend date, end pend date, status, tracking number,

forms

frmSpecialist: button(pending information)


frmPend: tracking number, recordno, start pend date, end pend date, button(pend/unpend), status(checkbox)


would I be able to take the record number produced by the dlookup and open up that record? Or should I choose an alternate method?

 

[Pampers]

would I be able to take the record number produced by the dlookup and open up that record? Or should I choose an alternate method?

I think so..
Look at the 3th argument of
docmd.openform

Pampers
Keeping it simple can be complicated

 

[TheAceMan1]

How are ya Trixxnixon . . .

You could also use the 4th arguement:

[blue]   DoCmd.OpenForm "PendFormName", , , "[TrackingNo] = " & Me!TrackingNo[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Pampers]

Thanx Ace, me counting is rusty. The 4th argument will do fine.

Pampers
Keeping it simple can be complicated