DoCmd.OpenForm question

[Rich]

Hi,

Hopefully someone could assist me on this. I have a field that when
filled in with a value, opens another form using that value as link
criteria. When I trace the code in the Form_Open routine, it doesn't
have the linked field value (the record that was supposed to be opened),
but it starts at the first record.

How can I get it to open on the record (i.e. Bill#) that I have opened
it with the Link Criteria with?

Is this the way the on Open routine works (i.e. doesn't have a record in
memory the 1st time)?


i.e.

'from field, 'BILL' on form 'POSTAPAYMENT'

Private Sub Bill_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EnterPayments"
stLinkCriteria = "[BILL#]= " & Me![Bill]

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "POSTAPAYMENT"
End Sub

'------------------------
'from form 'EnterPayments'

Private Sub Form_Open(CANCEL As Integer)
'Set frmResize = ADHResize2K.CreateFormResize()
'Set frmResize.Form = Me

'[BILL#].SetFocus
' DoCmd.FindRecord G_CurrentBill

If BillPaidChk = True Then
MsgBox "THIS BILL HAS ALREADY BEEN PAID"

Forms!EnterPayments.PostedDate
Forms!EnterPayments.AllowEdits = False

....'more code not pasted here ...


End Sub
'--------------------------


Thanks,
RJC

 

[Jeanette Cunningham]

Hi Rich
there are a couple of things to watch out for.
The open statement does work the first time if you have the code correct for
it.

1. using special characters in names like [Bill#]
Access gets confused, it is best to avoid special characters in names for
anything in a database.
For example use BillNbr for the name of the field, the control and the
textbox.

2. If the bill number is stored in the table in a text field - as it often
is for bill numbers and serial numbers etc,
change the code to


stLinkCriteria = "[BILL#]= """ & Me![Bill] & """"

that is 3 double quotes in front of & Me![Bill] &
and 4 double quotes after & Me![Bill] &



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Rich]

Thanks Jeanette for the feedback. I agree with you on the special
characters; unfortunately, I didn't write or design the database and am
trying to fix issues after the fact. I never use any special characters
in my field names (except the underscore). I am cleaning up the fields
as I go, but some of them reach throughout the database and I don't want
to create more issues than what we already have. I might have to just
to eliminate issues.

The Bill field is long integer. I also tried it like you suggested
(prior to jumping to this forum) and that also didn't work either (same
situation).

Any other suggestions?

Thank you,
Rich

Hi Rich
there are a couple of things to watch out for.
The open statement does work the first time if you have the code correct for
it.

1. using special characters in names like [Bill#]
Access gets confused, it is best to avoid special characters in names for
anything in a database.
For example use BillNbr for the name of the field, the control and the
textbox.

2. If the bill number is stored in the table in a text field - as it often
is for bill numbers and serial numbers etc,
change the code to


stLinkCriteria = "[BILL#]= """ & Me![Bill] & """"

that is 3 double quotes in front of & Me![Bill] &
and 4 double quotes after & Me![Bill] &



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

Hopefully someone could assist me on this. I have a field that when
filled in with a value, opens another form using that value as link
criteria. When I trace the code in the Form_Open routine, it doesn't have
the linked field value (the record that was supposed to be opened), but it
starts at the first record.

How can I get it to open on the record (i.e. Bill#) that I have opened it
with the Link Criteria with?

Is this the way the on Open routine works (i.e. doesn't have a record in
memory the 1st time)?


i.e.

'from field, 'BILL' on form 'POSTAPAYMENT'

Private Sub Bill_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EnterPayments"
stLinkCriteria = "[BILL#]= " & Me![Bill]

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "POSTAPAYMENT"
End Sub

'------------------------
'from form 'EnterPayments'

Private Sub Form_Open(CANCEL As Integer)
'Set frmResize = ADHResize2K.CreateFormResize()
'Set frmResize.Form = Me

'[BILL#].SetFocus
' DoCmd.FindRecord G_CurrentBill

If BillPaidChk = True Then
MsgBox "THIS BILL HAS ALREADY BEEN PAID"

Forms!EnterPayments.PostedDate
Forms!EnterPayments.AllowEdits = False

...'more code not pasted here ...


End Sub
'--------------------------


Thanks,
RJC

 

[Jeanette Cunningham]

A bit of debugging

stLinkCriteria = "[BILL#]= """ & Me![Bill] & """"
Debug.Print stLinkCriteria

Add the debug.print to your code after the stLinkCriteria line.
Put a break point on the debug.print line.

In form view click the button or whatever to open EnterPayments.
Access will stop at the break point, press F8 and see what Access prints to
the immediate window.
See what access gets for stLinkCriteria.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Thanks Jeanette for the feedback. I agree with you on the special
characters; unfortunately, I didn't write or design the database and am
trying to fix issues after the fact. I never use any special characters
in my field names (except the underscore). I am cleaning up the fields as
I go, but some of them reach throughout the database and I don't want to
create more issues than what we already have. I might have to just to
eliminate issues.

The Bill field is long integer. I also tried it like you suggested (prior
to jumping to this forum) and that also didn't work either (same
situation).

Any other suggestions?

Thank you,
Rich

Hi Rich
there are a couple of things to watch out for.
The open statement does work the first time if you have the code correct
for it.

1. using special characters in names like [Bill#]
Access gets confused, it is best to avoid special characters in names for
anything in a database.
For example use BillNbr for the name of the field, the control and the
textbox.

2. If the bill number is stored in the table in a text field - as it
often is for bill numbers and serial numbers etc,
change the code to


stLinkCriteria = "[BILL#]= """ & Me![Bill] & """"

that is 3 double quotes in front of & Me![Bill] &
and 4 double quotes after & Me![Bill] &



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi,

Hopefully someone could assist me on this. I have a field that when
filled in with a value, opens another form using that value as link
criteria. When I trace the code in the Form_Open routine, it doesn't
have the linked field value (the record that was supposed to be opened),
but it starts at the first record.

How can I get it to open on the record (i.e. Bill#) that I have opened
it with the Link Criteria with?

Is this the way the on Open routine works (i.e. doesn't have a record in
memory the 1st time)?


i.e.

'from field, 'BILL' on form 'POSTAPAYMENT'

Private Sub Bill_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EnterPayments"
stLinkCriteria = "[BILL#]= " & Me![Bill]

DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "POSTAPAYMENT"
End Sub

'------------------------
'from form 'EnterPayments'

Private Sub Form_Open(CANCEL As Integer)
'Set frmResize = ADHResize2K.CreateFormResize()
'Set frmResize.Form = Me

'[BILL#].SetFocus
' DoCmd.FindRecord G_CurrentBill

If BillPaidChk = True Then
MsgBox "THIS BILL HAS ALREADY BEEN PAID"

Forms!EnterPayments.PostedDate
Forms!EnterPayments.AllowEdits = False

...'more code not pasted here ...


End Sub
'--------------------------


Thanks,
RJC