Using VBA to Open Form to Specific Record??

[VioLynne]

I'm new to programming and this forum, but it appears that people are willing to help newbies like me so I thought I'd give it a try.

I have 2 forms, the first is to enter a patient number, if it exists in the table, I want to open to the record, if not, I want to open the form to a new record. New records are not a problem. However, the second form does not load the record specified in the OpenForm criteria. The DCount works correctly for single and multiple field criteria.

I actually have a few pairs of these. On one form, it loads blank, on another it opens to the first record.

The strangest part is that I had this working yesterday afternoon, somehow broke it, fixed it today and just broke it again. Is there something outside my code that the form is looking to when it opens?

Thanks for any help!


Private Sub cmdEnter_Click()
If DCount("[PtID]", "tblPatients", "[PtID]='" & Me!enterPtID & "'") > 0 Then
MsgBox "Patient already in database." & vbCrLf & vbCrLf & "Please verify Data."

DoCmd.OpenForm "frmPatients", , , "[PtID]='" & Me!enterPtID & "'"

Else: MsgBox "Enter new patient Data"
DoCmd.OpenForm "frmPatients", , , , acFormAdd
Forms!frmPatients!txtPtID.Value = Forms!EnterPatient!enterPtID
End If

DoCmd.Close acForm, "EnterPatient"

End Sub

 

[namliam]

Your ID field is probably a number field, so you dont want to be using quotes
Instead of
DoCmd.OpenForm "frmPatients", , , "[PtID]='" & Me!enterPtID & "'"
try
DoCmd.OpenForm "frmPatients", , , "[PtID]=" & Me!enterPtID

On related notes:
- Use [ code ] and [/ code ] without the spaces when you post code on the forum
- Use prefixes on names, like tbl for tables, qry for queries and frm for forms etc. This will help you in the future. You allready do this but not consequently. Stick to this!
- Use Me.enterPtID instead of Me! it helps to prevent errors.
- Dont use Else: it is messy, just do Else and continue on the next line. This is much more friendly for maintenance.
- Indent your code, I dont know if you do this or not.. . but if then else (for example) should look like:

If ... then
    do something
else
    do something else
end if

Same with do while and select case for next etc...

Oh, and welcome to the wonderfull world of Access (programmers - forum)

 

[zanaeira]

I'm not exactly sure what you're trying to do so I'm sorry if this reply is unnecessary, but it sounds like this:

You have a form in which you enter a patient's ID, then another form opens up to that patient's record?

If that's the case then couldn't you set the record source for the second form to a parameter query which looks to the first form's Patient ID field? Or if you want to use VBA couldn't you use something similar to:

DoCmd.OpenForm "frmName", WhereCondition:=PatientID = Me.PatientID

The first PatientID being the field on the second form, and Me.PatientID referring to the field that you entered the Patient's ID in the first place.

I may have completely misunderstood your question in which case I'm sorry :S.

Anyways take care, hope you sort out your problem soon.

 

[VioLynne]

Thanks for your help and general coding suggestions. In my DoCmd.OpenForm I've specified acFormEdit and it works beautifully.