Access VBA: OpenForm to a specific record

[busybee]

I am not a specialist, but am trying to create an application with Access 2000to manage our company’s orders. I have a table called ‘Service’ containing descriptive data on the type of service requested. This table is linked to several other tables, with ‘ServiceNo’ as the primary key linking most of them. The service number is not an autonumber, but a counter incremented through code, for specific reasons that I won’t develop here.

I have created two forms: one to consult all the service records (F_Service_Consult), with a button to open another form that allows the user to add a new service record (F_Service_Add).

When the new service form opens, the service number counter is incremented and this number is assigned as the new 'ServiceNo' in a new record added to the ‘Service’ table (using the SQL ‘Insert’ command). I would like this form (F_Service_Add) to open on this record only. In the code that creates ‘ServiceNo’, the correct value is contained in a variable called ‘lngServiceCount’. I have tried using this code to open my form:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "F_Service_Add"
stLinkCriteria = "Forms![F_Service_Add]![ServiceNo]='" & lngServiceCount & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

When I run the code step-by-step, the correct value is shown for ‘lngServiceCount’ when the mouse hovers over the variable (for ex. ‘155’). When the form opens, I have 1 record (Filtered), but nothing shows in the ‘ServiceNo’ text box.

I have deactivated the code that is in the ‘OnCurrent’ event procedure of this form to avoid any interference.

There are many sub-forms on the main form, which also contain the ‘ServiceNo’ in text boxes.

When I use this command to open the form:

DoCmd.GoToRecord , , acLast

the form opens correctly, with the correct ‘ServiceNo’, but ALL the records are opened.

The variable type for ‘lngServiceCount’ is a long integer. (I hope that is the correct term in English, because I am using a French version of Access).

I would be very grateful if anyone could tell me what I’m doing wrong. I’ve tried to be clear, but if more information is required, I would of course provide what’s missing.
Thanks in advance.

 

[edtab]

Try using DMax to get the last service no. created into your textbox.

In the onload event of your form:

Me.textboxname = Dmax("[ServiceNoFieldName]","serviceTableName")

This should capture the last number created (highest value) in the table.

 

[busybee]

Not quite there yet

Thanks for your quick reply. After attempting what you've suggested, I find myself with (Filtered) at the bottom of my form window, but all the records are still displayed. And this also leads to a significant mixup in how the counter works - I'll spare you the details, because it's very messy. I'm going to check out why in detail, but wanted to thank you quickly for your reply. I'll keep you posted if anything good comes of all this.

 

[RoyVidar]

The filtering needs the name of a field in the recordsource, what happens with

stLinkCriteria = "ServiceNo='" & lngServiceCount & "'"

BTW, if ServiceNo is a numeric field, drop the single quotes

stLinkCriteria = "ServiceNo=" & lngServiceCount

 

[busybee]

Solution to opening form to a specific record

Thank you Roy, that works!

I was confused about the code, since I "borrowed" it from a forum somewhere and don't really understand what I'm writing. (It's amazing how far you can get doing that - until you really need to know what you're doing).