Open form and if no matching records create a new record

Open form and if no matching records create a new record

Post by ketb » Sun, 03 Feb 2008 05:51:29


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
 
 
 

Open form and if no matching records create a new record

Post by Jeanette C » Sun, 03 Feb 2008 14:55:57

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

 
 
 

Open form and if no matching records create a new record

Post by ketb » Thu, 07 Feb 2008 05:57:54

On Feb 2, 12:55燼m, "Jeanette Cunningham"




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.
 
 
 

Open form and if no matching records create a new record

Post by Jeanette C » Thu, 07 Feb 2008 06:33:34

sn'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

"ketbd" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...
On Feb 2, 12:55 am, "Jeanette Cunningham"
< XXXX@XXXXX.COM > wrote:

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.