Access VBA: OpenForm to a specific record

Joined
Dec 26, 2006
Messages
2
Reaction score
0
I am not a specialist, but am trying to create an application with Access 2000 to 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.
 
Joined
Dec 26, 2006
Messages
2
Reaction score
0
I received a reply in another forum, but for reference, here is the solution:

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

or, since ServiceNo is a numeric field, I dropped the single quotes:

stLinkCriteria = "ServiceNo=" & lngServiceCount
 
Joined
Dec 22, 2006
Messages
7
Reaction score
0
If you are opening the form to add a new service and taking a service number from a field on the current form which you are calling the new form from. I would have done it a different way.

docmd.openform "F_Service_Add" , , , , acformadd ; This line will open the f_Service_add form and enterin to add mode
form_f_service_add.serviceno = me.lngservicecount ; This should then populate the new record with the service number from the field on the current form and put it into the field on the newly opened form.

This should get you away from using that insery sql command you mentioned earlier.

If you are also going to be opening the form f_service_add directly with out the other form, you could put a event on the on insert event, to lookup and retrieve the next service number, ie if its incremental based on the last service number recorded you should use the dmax function and increment by 1

Another way is to pass the service number in a open arg (docmd.openform [formname],,,,,,[field or var to pass to the form]

Hope this help


Life is to short, keep it simple.
 

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

Top