fomr opens to wrong record

[Walter]

I have a command button on a form to open another form to a specific record
to get some information. The form opens to the same incorrect record each
time no matter which record is called. I've checked the variables and all
are correct. Here is my code:
Dim stDocName As String
Dim intTrip As Integer
Dim stLinkCriteria As String

stDocName = "frmTrips"
intTrip = DLookup("TripID", "qryGetTrailerRepairMileage2")
stLinkCriteria = "[TripID]= " & intTrip

DoCmd.OpenForm stDocName, , , , acFormReadOnly, acDialog, stLinkCriteria

I also tried setting the value of an unbound text box and filtering to that.
The text box displayed the correct record number but the form still opened
to the same record.

What am I missing?

 

[NevilleT]

Hi Walter

I would use a different approach. Create a query that uses the first form
text box that contains the reference to TripID. That way you only view one
record. Use the build feature in the query design to get the correct
reference to the form text box.

Neville Turbit
www.projectperfect.com.au

 

[Walter]

I tried that first.
Me.txtTripID = DLookup("TripID", "qryGetTrailerRepairMileage2") 'assign
record number to go to

Dim stDocName As String

Dim stLinkCriteria As String

stDocName = "frmTrips" 'form to open
stLinkCriteria = "[TripID]= " & Me![TxtTripID] 'filter criteria

DoCmd.OpenForm stDocName, , , , acFormReadOnly, acDialog, stLinkCriteria

I had the same results. The txtTripID would be 2064, the record would be
229 or the txtTripID would be 2068 and the record would be 229. Either way,
the form opens to record 229 even though the link criteria calls for a
different one.

--
Thanks for your help!
Walter

Hi Walter

I would use a different approach. Create a query that uses the first form
text box that contains the reference to TripID. That way you only view one
record. Use the build feature in the query design to get the correct
reference to the form text box.

Neville Turbit
www.projectperfect.com.au

I have a command button on a form to open another form to a specific record
to get some information. The form opens to the same incorrect record each
time no matter which record is called. I've checked the variables and all
are correct. Here is my code:
Dim stDocName As String
Dim intTrip As Integer
Dim stLinkCriteria As String

stDocName = "frmTrips"
intTrip = DLookup("TripID", "qryGetTrailerRepairMileage2")
stLinkCriteria = "[TripID]= " & intTrip

DoCmd.OpenForm stDocName, , , , acFormReadOnly, acDialog, stLinkCriteria

I also tried setting the value of an unbound text box and filtering to that.
The text box displayed the correct record number but the form still opened
to the same record.

What am I missing?

 

[Walter]

The query "qryGetTrailerRepairMileage2" is the third in a series that returns
only 1 record. The criteria are built in the various queries.
--
Thanks for your help!
Walter

Hi -

Your problem is probably in the Dlookup. You have not included any criteria
in the DLookup function, so it always returns the TripID of the first record
it finds in the query. You need to the the third parameter for dlookup, the
critera, to specify which record you want, e.g.

intTrip = DLookup("TripID", "qryGetTrailerRepairMileage2","TripNumber = " &
me!Myformfield)

or something like it.

HTH

John

I have a command button on a form to open another form to a specific record
to get some information. The form opens to the same incorrect record each
time no matter which record is called. I've checked the variables and all
are correct. Here is my code:
Dim stDocName As String
Dim intTrip As Integer
Dim stLinkCriteria As String

stDocName = "frmTrips"
intTrip = DLookup("TripID", "qryGetTrailerRepairMileage2")
stLinkCriteria = "[TripID]= " & intTrip

DoCmd.OpenForm stDocName, , , , acFormReadOnly, acDialog, stLinkCriteria

I also tried setting the value of an unbound text box and filtering to that.
The text box displayed the correct record number but the form still opened
to the same record.

What am I missing?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.

 

[Walter]

Questions answered in line.
--
Thanks for your help!
Walter

Hi -

Two questions-

1) Is there any code in the frmTrips form that might be changing the form's
recordsource?

Not that I know of. The form opens to a new record normally. I added code
to test for the calling form being loaded and if so disregard going to a new
record. The only other thing is a CarryOver routine to fill in certain
fields based on tyhe last record entered.

2) Can you run the Dlookup in the immediate pane (Ctrl-G) to verify that it
is working correctly? Since the query "qryGetTrailerRepairMileage2" is
derived from other queries, maybe one of them has an error in it, so you'll
have to backtrack a bit. Where do those other queries get their criteria
from?

When I put a code break in to test the variables, the query is returning the
correct TripID. The link criteria shows the correct record to open. While
the form from which the criteria comes is open, and I open the query, it
returns the same correct TripID. I have veryfied this by looking at the
table. The criteria for the query comes from a service data entry form.
Depending on certain conditions, another form opens to provide information
needed to adjust a calculated field on the data entry form. If you need to
get this information, frmTrips needs to open to the correct record. The
criteria, which is the service date and the vehicle number, for the queries
comes from the service data enrty form. The query then returns the last
trip(mileage records) for that vehicle that is less than or equal to that
date. The record that is opening is not even one for the specific vehicle in
question. It is not the first record either. It just seems to be some
random record but it's always the same one even with different vehicles and
different dates.
Sorry for the long post but I wanted to give you as much information as I
could.

John

The query "qryGetTrailerRepairMileage2" is the third in a series that returns
only 1 record. The criteria are built in the various queries.

Hi -

[quoted text clipped - 31 lines]

What am I missing?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.

 

[Walter]

Yes that did it! I added the acFormReadOnly and acDialog after the wizard
set it up originally.

--
Thanks for your help!
Walter

Hi Walter -

Talk about overlooking the obvious on my part!

Your docmd.openform statement is wrong. The "Where" argument is the 4th
argument, not the 7th as you have it. Try it like this:

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly, acDialog

John

Questions answered in line.

Hi -

Two questions-

1) Is there any code in the frmTrips form that might be changing the form's
recordsource?

Not that I know of. The form opens to a new record normally. I added code
to test for the calling form being loaded and if so disregard going to a new
record. The only other thing is a CarryOver routine to fill in certain
fields based on tyhe last record entered.

2) Can you run the Dlookup in the immediate pane (Ctrl-G) to verify that it
is working correctly? Since the query "qryGetTrailerRepairMileage2" is
derived from other queries, maybe one of them has an error in it, so you'll
have to backtrack a bit. Where do those other queries get their criteria
from?

When I put a code break in to test the variables, the query is returning the
correct TripID. The link criteria shows the correct record to open. While
the form from which the criteria comes is open, and I open the query, it
returns the same correct TripID. I have veryfied this by looking at the
table. The criteria for the query comes from a service data entry form.
Depending on certain conditions, another form opens to provide information
needed to adjust a calculated field on the data entry form. If you need to
get this information, frmTrips needs to open to the correct record. The
criteria, which is the service date and the vehicle number, for the queries
comes from the service data enrty form. The query then returns the last
trip(mileage records) for that vehicle that is less than or equal to that
date. The record that is opening is not even one for the specific vehicle in
question. It is not the first record either. It just seems to be some
random record but it's always the same one even with different vehicles and
different dates.
Sorry for the long post but I wanted to give you as much information as I
could.

John

[quoted text clipped - 5 lines]

What am I missing?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com


.