Open a form with current record

[hilbertm]

I have a form that contains airport information. It opens with a query where the user selects the airport to view. Once the form opens, the user can navigate through the instrument procedures at the selected airport (on the main form). I have a sub form (on a tab control) linked to the airport info on the main form to show ptas information for each instrument procedure. I would like to have a form open (PTASEnter form)when a cmd button is pressed to enter new ptas information for the instrument procedure that is displayed on the main form.

The ptas table and the instrument procedure table are linked with the field PROCEDURE ID

I am using the following code to open a form for the user to enter data.

Private Sub EnterNewPTAS_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "PTASEnter"
stLinkCriteria = "[PROCEDURE ID]=" & Me![PROCEDURE ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_EnterNewPTAS_Click:
Exit Sub
Err_EnterNewPTAS_Click:
MsgBox Err.Description
Resume Exit_EnterNewPTAS_Click
End Sub

The procedure ID on the PTASEnter form does not match the procedure ID on the main form

I want the form to open with a new record so the user can enter new ptas information.

Thanks

 

[rich.barry]

I'm not entirely clear on what you want, but to go to a new record is:

DoCmd.GoToRecord acDataForm, stDocName, acNewRec

 

[hilbertm]

Rich,
The problem is that the
DoCmd.GoToRecord acDataForm, stDocName, acNewRec
code brings me to a new record with a procedure ID of 0.
My main form is linked to my sub form by procedure ID.If I have a procedure record on my main form with a procedure ID of 193, the PTAS info which is on the sub form(also in a diffenrnt table) is the PTAS info for procedure 193. My problem is that when I use the GoToRecord from the subform I don't know how to control which record I goto.
I want the PTAS subform to be read only, and I want the form that opens after clicking a cmd button to open at the PTAS record the user is currently viewing. I also want a button to add a new record to the same procedure that is open in the main form.

I hope this is a clear explaination of what I am trying to do.

Thanks for taking the time for my problem.
Mike

 

[Rich]

Put the button for new sub form records on the sub form footer.

 

[hilbertm]

Rich,
I put the cmd button on the subform footer, but I still can't get to the exact record that the user is viewing on the sub form. If the user is viewing record 3 of 4, I can only go to the first or last record, not the one the user is viewing. I guess I could have the user just cycle through until they get to the PTAS record they want to edit, but I was hoping for more control.
I still have the same problem of getting a procedure ID of 0 when trying to make a new record. This means that the user would have to re-enter the procedure name when adding a new PTAS. This seems redundant to me since the user is looking at a form that has a button on it that says add a new PTAS for this procedure at this airport. Then I would have to ask the user to enter the procedure name and airport when addng the new PTAS. I think my problem may be that the PTAS info and procedure info are in seperate tables.
Am I asking too much of Access?

 

[Rich]

This one looks interesting, if you can save a copy in 97 and zip it then I'll take a look for you the email is richiet@lineone.net

 

[Pat Hartman]

You need the unique identifier for the record being displayed on the subform. This may be two fields. If that is the case, change the stLinkCriteria to something like:

stLinkCriteria = "[PROCEDURE ID] = " & Me.[PROCEDURE ID] & " AND [OTHER FIELD] = " & Me.OtherField

Don't forget - if either field is text, it needs to be surrounded by single or double quotes!

 

[hilbertm]

Pat,
Thanks for the info. I tried it but I must be doing something wrong. I got the following error message:
Syntax error(missing operator0in query expression'{PROCEDURE ID]=193AND'[PTAS Number]'=rkso test 1a'.
193 is the procedure id I want to link with rkso test 1a, so I must be getting close. I may have put my quotes in the wrong place since PTAS field is text.
This is what I used to generate the above error:
stLinkCriteria = "[PROCEDURE ID] = " & Me.[PROCEDURE ID] & " AND '[PTAS Number]'= " & Me.[PTAS Number]

Thanks

 

[Rich]

O.K it's early in the morning here so i've only had chance to take a quick look. Your problem stems from the fact that when you open PTAS enter the ProcedureID is not automatically entered, there are two ways to approach this, create another form which has ProcedureID as it's record source, put PTAS enter as a sub form and link to ProcedureID any new records entered will then display PrID. Or set the ID when opening PTAS enter,
i.e.DoCmd.GoToRecord acDataForm, stDocName, acNewRec
Forms!PTASEnter!ProcedureID = Me.ProcedureID
I prefer the first method, if no new data is entered then you don't have to deal with redundant entries.
There are a couple of other points worthy of note there are spaces in your field names which access doesn't like, try and avoid them. When you use the wizards to build forms access gives the controls the same names as the fields which can then cause confusion e.g. PROCEDURE ID I would change to txtProcedureID: much easier to deal with.
You application looks pretty professional to me, even if I didn't understand the codes. I thought flying was easy

Unless your going to move into the client/server area don't bother "upgrading" to A2K.
HTH

 

[hilbertm]

Rich,
thanks for taking a look, It is getting late here (we must be on opposite ends of the world), so I will check into your suggestions tomorrow. I am going to change my names to get rid of spaces. (Hey I'm learning as I go

)
Flying is the easy part, designing the instrument approaches the pilots use and designing the access database the Terminal Instrument Procedures Specialists (TERPS) are the difficult things for me.

thanks again
Mike