Open form and if no matching records create a new record

[ketbd]

I am sure this is simple for most of you but I am stumped.

I have a form PT_Demog that needs to open another form PT_MedBasic
I created a button using the wizard to open records with Patient_ID.

Private Sub Medical_Form_Click()
On Error GoTo Err_Medical_Form_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PT_MedBasic"

stLinkCriteria = "[Patient_ID]=" & Me![Patient_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Const cQuote = """" 'Thats two quotes
Me!Patient_ID.DefaultValue = cQuote & Forms![PT_MedBasic].
[Patient_ID].Value & cQuote

Exit_Medical_Form_Click:
Exit Sub

Err_Medical_Form_Click:
MsgBox Err.Description
Resume Exit_Medical_Form_Click

End Sub


When I open the form I want it to search for the matching records
(which it does) but if there aren't any that match I would like it to
create one that copies the Patient_ID from PT_Demog to PT_MedBasic.
This is a one to many relationship. Any suggestions?

Thanks,
Laurie

 

[Jeanette Cunningham]

First test if there are any matching records in the form's recordsource.
If there is at least matching record, run the code to open the form using
the stLinkCriteria.
Use a DCount to test if there are any records.

If there are no matching records, run code to open the form at a new record
DoCmd.OpenForm stDocName
If you only want users to add one record use:
DoCmd.OpenForm stDocName, , , , acFormAdd

If you want users to be able to add several records, pass a literal in the
openargs to open the form at a new record.
DoCmd.OpenForm stDocName, , , , , ,"AddNew"

On the form that is going to open, have code that reads the OpenArgs and
takes the appropriate action
In the form's load event
If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "AddNew" Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If
End If

Jeanette Cunningham

 

[ketbd]

First test if there are any matching records in the form's recordsource.
If there is at least matchingrecord, run the code to open the form using
the stLinkCriteria.
Use a DCount to test if there are any records.

If there are no matching records, run code to open the form at a newrecord
DoCmd.OpenForm stDocName
If you only want users to add onerecorduse:
DoCmd.OpenForm stDocName, , , , acFormAdd

If you want users to be able to add several records, pass a literal in the
openargs to open the form at a newrecord.
DoCmd.OpenForm stDocName, , , , , ,"AddNew"

On the form that is going to open, have code that reads the OpenArgs and
takes the appropriate action
In the form's load event
    If Not IsNull(Me.OpenArgs) Then
        If Me.OpenArgs = "AddNew" Then
            DoCmd.GoToRecord acActiveDataObject, , acNewRec
        End If
End If

Jeanette Cunningham

I am sure this is simple for most of you but I am stumped.

I have a form PT_Demog that needs to open another form PT_MedBasic
I created a button using the wizard to open records with Patient_ID.

Private Sub Medical_Form_Click()
On Error GoTo Err_Medical_Form_Click

   Dim stDocName As String
   Dim stLinkCriteria As String

   stDocName = "PT_MedBasic"

   stLinkCriteria = "[Patient_ID]=" & Me![Patient_ID]
   DoCmd.OpenForm stDocName, , , stLinkCriteria
   Const cQuote = """"  'Thats two quotes
   Me!Patient_ID.DefaultValue = cQuote & Forms![PT_MedBasic].
[Patient_ID].Value & cQuote

Exit_Medical_Form_Click:
   Exit Sub

Err_Medical_Form_Click:
   MsgBox Err.Description
   Resume Exit_Medical_Form_Click

End Sub

When I open the form I want it to search for the matching records
(which it does) but if there aren't any that match I would like it to
create one that copies the Patient_ID from PT_Demog to PT_MedBasic.
This is a one to many relationship.  Any suggestions?

Thanks,
Laurie- Hide quoted text -

- Show quoted text -

Thanks, we are trying to incorporate your suggestions. I am
struggling with after the DCount if there is a record to open up the
form but if there isnt one to open up a new record. i used your code
suggestions but I am obviously missing something as it still isnt
working right.

 

[Jeanette Cunningham]

Isn't working right - isn't much to go on.
Is there an error message?
Does the form fail to open?
Does the form open but it is missing something?
Do you want the from to open with a default value for Patient_ID?

suggestion:
Replace DoCmd.OpenForm stDocName, , , , , ,"AddNew"
With DoCmd.OpenForm stDocName, , , , , , "AddNew" & "|" & Me.txtPatient_ID

Explanation:
pass 2 pieces of information in the open args, the value for Patient_ID as
well as the instruction to open at a new record.
I have used txtPatient_ID as the name of the control for Patient_ID on the
main form, replace it with the name on your form.

On the form that gets opened change the following code:
If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "AddNew" Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If
End If

to:

If Not IsNull(Me.OpenArgs) Then
If Split(Me.OpenArgs, "|")(0) = "AddNew" Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
Me.txtPatient_ID.DefaultValue = Split(Me.OpenArgs, "|")(1)
End If
End If

Assuming you are using Access 2000 or later.
Replace txtPatient_ID with the name of the control for Patient_ID on the
form that gets opened.

Jeanette Cunningham

First test if there are any matching records in the form's recordsource.
If there is at least matchingrecord, run the code to open the form using
the stLinkCriteria.
Use a DCount to test if there are any records.

If there are no matching records, run code to open the form at a newrecord
DoCmd.OpenForm stDocName
If you only want users to add onerecorduse:
DoCmd.OpenForm stDocName, , , , acFormAdd

If you want users to be able to add several records, pass a literal in the
openargs to open the form at a newrecord.
DoCmd.OpenForm stDocName, , , , , ,"AddNew"

On the form that is going to open, have code that reads the OpenArgs and
takes the appropriate action
In the form's load event
If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "AddNew" Then
DoCmd.GoToRecord acActiveDataObject, , acNewRec
End If
End If

Jeanette Cunningham

I am sure this is simple for most of you but I am stumped.

I have a form PT_Demog that needs to open another form PT_MedBasic
I created a button using the wizard to open records with Patient_ID.

Private Sub Medical_Form_Click()
On Error GoTo Err_Medical_Form_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "PT_MedBasic"

stLinkCriteria = "[Patient_ID]=" & Me![Patient_ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Const cQuote = """" 'Thats two quotes
Me!Patient_ID.DefaultValue = cQuote & Forms![PT_MedBasic].
[Patient_ID].Value & cQuote

Exit_Medical_Form_Click:
Exit Sub

Err_Medical_Form_Click:
MsgBox Err.Description
Resume Exit_Medical_Form_Click

End Sub

When I open the form I want it to search for the matching records
(which it does) but if there aren't any that match I would like it to
create one that copies the Patient_ID from PT_Demog to PT_MedBasic.
This is a one to many relationship. Any suggestions?

Thanks,
Laurie- Hide quoted text -

- Show quoted text -

Thanks, we are trying to incorporate your suggestions. I am
struggling with after the DCount if there is a record to open up the
form but if there isnt one to open up a new record. i used your code
suggestions but I am obviously missing something as it still isnt
working right.