Getting to the Correct Record

[Guest]

Below is the code I'm using to go to a specific record in another form the
relies on the same query that lstVisitComplications does. I want to be able
to dblClk on a specific record in the lstBox have the
frmVisitComplicationDetail open to that specific record. The code below
doesn't work. It opens the form but not to the specific record that was
dblClk'd in the listBox.

Thanks in advance for your help,

Robert


Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)


End Sub

 

[Rick Brandt]

Below is the code I'm using to go to a specific record in another
form the relies on the same query that lstVisitComplications does. I
want to be able to dblClk on a specific record in the lstBox have the
frmVisitComplicationDetail open to that specific record. The code
below doesn't work. It opens the form but not to the specific record
that was dblClk'd in the listBox.

Thanks in advance for your help,

Robert


Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)


End Sub

You have to set the value of stLinkCriteria BEFORE you use it to open the form.

 

[Guest]

How do I do that. Sorry for the ignorance on my part.

Thanks, Rob

Below is the code I'm using to go to a specific record in another
form the relies on the same query that lstVisitComplications does. I
want to be able to dblClk on a specific record in the lstBox have the
frmVisitComplicationDetail open to that specific record. The code
below doesn't work. It opens the form but not to the specific record
that was dblClk'd in the listBox.

Thanks in advance for your help,

Robert


Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)


End Sub

You have to set the value of stLinkCriteria BEFORE you use it to open the form.

 

[Rick Brandt]

How do I do that. Sorry for the ignorance on my part.

Just look at the lines of code you are using.

************************************************
Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)
**************************************************

The last two lines need to be swapped because your second-to-last line is using
the variable that is not being set until the last line.

 

[Guest]

I swaped as suggested, but it still doesn't work. What am I doing wrong?

Thanks, Robert

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = Me.lstVisitComplications.Column(0) &
"[fldVisitComplicationsNo]="


End Sub

How do I do that. Sorry for the ignorance on my part.

Just look at the lines of code you are using.

************************************************
Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)
**************************************************

The last two lines need to be swapped because your second-to-last line is using
the variable that is not being set until the last line.

 

[Rick Brandt]

I swaped as suggested, but it still doesn't work. What am I doing
wrong?

The lines you swapped were actually ONE line that was wrapped in my post. You
need...

(this next is all one line)
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)

....followed by...

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Guest]

Here's how it looks now and it still doesn't work. I think in the form
frmVisitComplicationsDetail in the fldVisitComplicationsNo that I might need
to set the criteria somehow. When I open the form fldVisitComplicationsNo is
set to 0 with nothing else in it, not the appropriate Id Number.

Sorry to be so much trouble, but your help is much appreciated. Thanks, Rob

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"

stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

I swaped as suggested, but it still doesn't work. What am I doing
wrong?

The lines you swapped were actually ONE line that was wrapped in my post. You
need...

(this next is all one line)
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)

....followed by...

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Rick Brandt]

Here's how it looks now and it still doesn't work. I think in the form
frmVisitComplicationsDetail in the fldVisitComplicationsNo that I
might need to set the criteria somehow. When I open the form
fldVisitComplicationsNo is set to 0 with nothing else in it, not the
appropriate Id Number.

Sorry to be so much trouble, but your help is much appreciated.
Thanks, Rob

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"

stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

I swaped as suggested, but it still doesn't work. What am I doing
wrong?

The lines you swapped were actually ONE line that was wrapped in my
post. You need...

(this next is all one line)
stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)

....followed by...

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Rick Brandt]

Here's how it looks now and it still doesn't work. I think in the form
frmVisitComplicationsDetail in the fldVisitComplicationsNo that I
might need to set the criteria somehow. When I open the form
fldVisitComplicationsNo is set to 0 with nothing else in it, not the
appropriate Id Number.

Sorry to be so much trouble, but your help is much appreciated.
Thanks, Rob

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"

stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Copy and paste *exactly* this code below. I have added a line continuation
character to eliminate any line-wrapping issues.

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
stLinkCriteria = "[fldVisitComplicationsNo]=" & _
Me.lstVisitComplications.Column(0)
MsgBox(stLinkCriteria)
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

I also added a message box to display what is in the stLinkCriteria variable.
Does that look like what you think it should? Post what the message box
displays.

 

[Guest]

I placed the code as you said and the msgBox gives me the right record
number, however, the form doesn't load with this number. The
fldVisitComplicationsNo in my frmVisitComplicationsDetail still opens with 0
in the field and opens to a new record.

Thanks for all your help, but I still need help.

Here's how it looks now and it still doesn't work. I think in the form
frmVisitComplicationsDetail in the fldVisitComplicationsNo that I
might need to set the criteria somehow. When I open the form
fldVisitComplicationsNo is set to 0 with nothing else in it, not the
appropriate Id Number.

Sorry to be so much trouble, but your help is much appreciated.
Thanks, Rob

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"

stLinkCriteria = "[fldVisitComplicationsNo]=" &
Me.lstVisitComplications.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

Copy and paste *exactly* this code below. I have added a line continuation
character to eliminate any line-wrapping issues.

Private Sub lstVisitComplications_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmVisitComplicationsDetail"
stLinkCriteria = "[fldVisitComplicationsNo]=" & _
Me.lstVisitComplications.Column(0)
MsgBox(stLinkCriteria)
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub

I also added a message box to display what is in the stLinkCriteria variable.
Does that look like what you think it should? Post what the message box
displays.

 

[Rick Brandt]

I placed the code as you said and the msgBox gives me the right record
number, however, the form doesn't load with this number. The
fldVisitComplicationsNo in my frmVisitComplicationsDetail still opens
with 0 in the field and opens to a new record.

Thanks for all your help, but I still need help.

The message box should not only give a number. It should display something
like...

[fldVisitComplicationsNo]=123

Is that what the message box text looks?

If so, then having the second form open to a new record would indicate that the
second form's Recordsource has NO record where the [fldVisitComplicationsNo]
field is equal to 123.

 

[Guest]

It displays as you said, fldVisitComplicationsNo]=123, but the form loads up
to a new record. This is very strange as the frmVisitComplications and
frmVisitComplicationDetails are based on the same query.

I am lost as to what is the problem.

Richard, thankyou so much for your patience with this rooky. Robert

I placed the code as you said and the msgBox gives me the right record
number, however, the form doesn't load with this number. The
fldVisitComplicationsNo in my frmVisitComplicationsDetail still opens
with 0 in the field and opens to a new record.

Thanks for all your help, but I still need help.

The message box should not only give a number. It should display something
like...

[fldVisitComplicationsNo]=123

Is that what the message box text looks?

If so, then having the second form open to a new record would indicate that the
second form's Recordsource has NO record where the [fldVisitComplicationsNo]
field is equal to 123.

 

[Guest]

My Dearest Richard,
Your solution works absolutely prefect when I realized I had the record
source wrong. I didn't have it to the same query as the initial form as I
indicated. When I changed it; Magic! Thank you and I wish you success in
every thing you do. You are a God send.

Thank, Robert

I placed the code as you said and the msgBox gives me the right record
number, however, the form doesn't load with this number. The
fldVisitComplicationsNo in my frmVisitComplicationsDetail still opens
with 0 in the field and opens to a new record.

Thanks for all your help, but I still need help.

The message box should not only give a number. It should display something
like...

[fldVisitComplicationsNo]=123

Is that what the message box text looks?

If so, then having the second form open to a new record would indicate that the
second form's Recordsource has NO record where the [fldVisitComplicationsNo]
field is equal to 123.

 

[Rick Brandt]

It displays as you said, fldVisitComplicationsNo]=123, but the form
loads up to a new record. This is very strange as the
frmVisitComplications and frmVisitComplicationDetails are based on
the same query.

I am lost as to what is the problem.

Richard, thankyou so much for your patience with this rooky. Robert

I assume that the missing "[" in your first sentence is just a typo in your
post.

My usual recomendation with this is to simplify the task to see what we can find
out. Open the second form from the db window and find an ID that you know to
exist, but something other than the first record. So for the sake of example
move to the fifth record and let's say that has an ID of 456.

Then try replacing the stLinkCriteria in your code with that hard-coded value...

DoCmd.OpenForm stDocName, , , "[fldVisitComplicationsNo]=456"

If you do this and that DOES open the form to the record with ID 456 then that
means that there is still something about your variable that is incorrect. Does
the ComboBox RowSource apply any formatting, like displaying 0123 instead of
123?

 

[Rick Brandt]

My Dearest Richard,
Your solution works absolutely prefect when I realized I had the
record source wrong. I didn't have it to the same query as the
initial form as I indicated. When I changed it; Magic! Thank you and
I wish you success in every thing you do. You are a God send.

Thank, Robert

Doh! Well, glad you got it sorted out.