Records always seen a 'new'?

G

Guest

Hello,

I have a simply database application where one can fill out a 'Patient
Info.' box with basic Patient ID info. (ID, initials, and site #) and then
have that info. auto-populated in a 'Patient Status' form once they click on
a Command Button on the 'Patient Info.' form. There are two Command Buttons,
actually at the bottom of the 'Patient Info' form-- one to open an existing
record, and one to open a New Record.

Here is the code used to open the 'Patient Status' form using the one of the
forementioned Command Button when I want the user to only be brought to an
existing record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

This is the code behind the other Command Button I have when I want to open
a NEW 'Patient Status' record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , , acAdd

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

In the 'Patient Status' form's 'On Current:' Event, I have the following code:

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Else
DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"
End If
End Sub

Where SetAutoValues(Me) looks like:

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site

End With

Exit_SetAutoValues:
Exit Sub

SetAutoValues_err:
MsgBox Err.Description
Resume Next

End Sub

I have stepped thru the code, and the 'If Me. NewRecord' is always seen as
true. What am I missing here so that one Command button will open a New
'Patient Status' record and the other Command button will find an existing
record?

Thanks.
 
D

Douglas J Steele

Assuming Id is a numeric field, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id

If it's text, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Chr$(34) &
Forms.fEnterPatientInfo.id & Chr$(34)
 
G

Guest

HI Doug,

Not sure what is going on, but I revised your code per your suggestion and
it is still pulling up a blank 'Patient Status' form. I know that Access
'knows' about these records, because if I try to save a record that violates
the Primary Key I have set, it gives the standard error saying that this
would be a duplicate record. Any other thoughts are certainly appreciated.

Thanks.
--
Pat Dools


Douglas J Steele said:
Assuming Id is a numeric field, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id

If it's text, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Chr$(34) &
Forms.fEnterPatientInfo.id & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pat Dools said:
Hello,

I have a simply database application where one can fill out a 'Patient
Info.' box with basic Patient ID info. (ID, initials, and site #) and then
have that info. auto-populated in a 'Patient Status' form once they click on
a Command Button on the 'Patient Info.' form. There are two Command Buttons,
actually at the bottom of the 'Patient Info' form-- one to open an existing
record, and one to open a New Record.

Here is the code used to open the 'Patient Status' form using the one of the
forementioned Command Button when I want the user to only be brought to an
existing record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

This is the code behind the other Command Button I have when I want to open
a NEW 'Patient Status' record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , , acAdd

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

In the 'Patient Status' form's 'On Current:' Event, I have the following code:

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Else
DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"
End If
End Sub

Where SetAutoValues(Me) looks like:

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site

End With

Exit_SetAutoValues:
Exit Sub

SetAutoValues_err:
MsgBox Err.Description
Resume Next

End Sub

I have stepped thru the code, and the 'If Me. NewRecord' is always seen as
true. What am I missing here so that one Command button will open a New
'Patient Status' record and the other Command button will find an existing
record?

Thanks.
Click to expand...
Click to expand...
 
D

Douglas J Steele

Do you have a value for Forms.fEnterPatientInfo.id?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pat Dools said:
HI Doug,

Not sure what is going on, but I revised your code per your suggestion and
it is still pulling up a blank 'Patient Status' form. I know that Access
'knows' about these records, because if I try to save a record that violates
the Primary Key I have set, it gives the standard error saying that this
would be a duplicate record. Any other thoughts are certainly appreciated.

Thanks.
--
Pat Dools


Douglas J Steele said:
Assuming Id is a numeric field, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id

If it's text, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Chr$(34) &
Forms.fEnterPatientInfo.id & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pat Dools said:
Hello,

I have a simply database application where one can fill out a 'Patient
Info.' box with basic Patient ID info. (ID, initials, and site #) and then
have that info. auto-populated in a 'Patient Status' form once they
Click to expand...
click
on
a Command Button on the 'Patient Info.' form. There are two Command Buttons,
actually at the bottom of the 'Patient Info' form-- one to open an existing
record, and one to open a New Record.

Here is the code used to open the 'Patient Status' form using the one
Click to expand...
of
the
forementioned Command Button when I want the user to only be brought to an
existing record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

This is the code behind the other Command Button I have when I want to open
a NEW 'Patient Status' record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , , acAdd

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

In the 'Patient Status' form's 'On Current:' Event, I have the
Click to expand...
following
code:
Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Else
DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"
End If
End Sub

Where SetAutoValues(Me) looks like:

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site

End With

Exit_SetAutoValues:
Exit Sub

SetAutoValues_err:
MsgBox Err.Description
Resume Next

End Sub

I have stepped thru the code, and the 'If Me. NewRecord' is always seen as
true. What am I missing here so that one Command button will open a New
'Patient Status' record and the other Command button will find an existing
record?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Doug,

In looking at this post, I think I left out too much info. I just put
another post out there, if you'd like a better explanation of the problem I'm
having. In any case, thank you kindly for responding to this post.
Ultimately, I'm just trying to take multiple fields from a form and use them
to either auto-populate the same-named fields on a different form, or, if the
record already exists, find that record so that it can be edited, etc. Sorry
if this post was confusing and thanks again. This forum has been a life
saver on more than one occaision.
--
Pat Dools


Douglas J Steele said:
Do you have a value for Forms.fEnterPatientInfo.id?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pat Dools said:
HI Doug,

Not sure what is going on, but I revised your code per your suggestion and
it is still pulling up a blank 'Patient Status' form. I know that Access
'knows' about these records, because if I try to save a record that violates
the Primary Key I have set, it gives the standard error saying that this
would be a duplicate record. Any other thoughts are certainly appreciated.

Thanks.
--
Pat Dools


Douglas J Steele said:
Assuming Id is a numeric field, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id

If it's text, try:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Chr$(34) &
Forms.fEnterPatientInfo.id & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I have a simply database application where one can fill out a 'Patient
Info.' box with basic Patient ID info. (ID, initials, and site #) and then
have that info. auto-populated in a 'Patient Status' form once they click
on
a Command Button on the 'Patient Info.' form. There are two Command
Buttons,
actually at the bottom of the 'Patient Info' form-- one to open an
existing
record, and one to open a New Record.

Here is the code used to open the 'Patient Status' form using the one of
the
forementioned Command Button when I want the user to only be brought to an
existing record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

This is the code behind the other Command Button I have when I want to
open
a NEW 'Patient Status' record:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

DoCmd.OpenForm "FCRFStatus", , , , acAdd

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

In the 'Patient Status' form's 'On Current:' Event, I have the following
code:

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
Else
DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"
End If
End Sub

Where SetAutoValues(Me) looks like:

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site

End With

Exit_SetAutoValues:
Exit Sub

SetAutoValues_err:
MsgBox Err.Description
Resume Next

End Sub

I have stepped thru the code, and the 'If Me. NewRecord' is always seen as
true. What am I missing here so that one Command button will open a New
'Patient Status' record and the other Command button will find an existing
record?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding existing records 7
New Record not allowing data entry? 6
Locking records 3
Auto-populating fields on next form in series? 4
Filter records on Patient ID and Record Status 3
How to check in advance to see if a record already exists? 3
Autofill a new Record in a 2nd form from a different form 1
Code looping back to itself unexpectedly 3

Top