Access a query from VBA procedure in form button

[cesarone82]

Hi,

I have a button in an access form (frm_projects) with a VBA procedure, inside this procedure i need to select specific data from a join query (qry_projprof where a certain field in the query in equal to the projectnumber in the opened form which is Me!pjnumber).
The question is: do i need to call the openrecordset inside access or there are quicker ways? (I know in word to access the database I should use the open recordset function)
Once I am able to access the query in the VBA procedure How can I refer to the single query rows/column?

The button is basically filling forms in a PDF file from an acccess database

Thanks file in attached link in case

[pbaldy]

I can't look at the attachments right now, but you can use DLookup() or a recordset to get values from the query. If you're after a single value, I'd probably use the DLookup(), for multiple the recordset. Either way you'd use a criteria to find the specific record you want.

http://www.theaccessweb.com/general/gen0018.htm

[Micron]

Some issues there. Your project contains a reference to a library that I don't have so things don't run so well. Will try to answer your question instead. You can get at record field values with DLookup (although should not do so inside of a query) or create a recordset or use the form recordset clone.

Your query contains [Me]![codproj] as criteria. Queries cannot use Me as a reference - that is only for forms or reports to refer to themselves. Use the complete Forms syntax. I would have suggested something but can't tell which form to replace Me with. None of your forms seem to use the query as a recordsource, so that's a bit of a mystery at present.

EDIT - None of your forms seem to have codproj in them either, so it's a mystery why you're trying to use that field reference as query criteria.

[cesarone82]

Some issues there. Your project contains a reference to a library that I don't have so things don't run so well. Will try to answer your question instead. You can get at record field values with DLookup (although should not do so inside of a query) or create a recordset or use the form recordset clone.

Your query contains [Me]![codproj] as criteria. Queries cannot use Me as a reference - that is only for forms or reports to refer to themselves. Use the complete Forms syntax. I would have suggested something but can't tell which form to replace Me with. None of your forms seem to use the query as a recordsource, so that's a bit of a mystery at present.

EDIT - None of your forms seem to have codproj in them either, so it's a mystery why you're trying to use that field reference as query criteria.

Thanks pbaldi and micron.

Yes sorry I forgot to mention but i removed the reference in the file attached, basically it was just lines of code writing database data into a pdf form.

So two questions, I understood i need to use dlookup:

1) In the file attached I also put in the form a subform that has the data i need to extract, basically I need to extract certain values of certain records of this subform (which is the last one of the frm_projects
2) in case i don't have this subform on the form itself, how can i reference the query directly from the click procedure passing the pjnumber as parameter? for example, i put a second button in the form, which i d like to open the query associating pjnumber of the form to pjnumber in the query.
3) Dlookup can allow me to extract data in sequence withous specifying a parameter? i mean first record, then second record, etc.

Thanks in advance.

[pbaldy]

I'm still not in a position to look at the attachment, but the link I posted shows how to refer to a form to get the criteria. In answer to your last question, no. Without a criteria DLookup() will always return the first record it finds.

[Welshgasman]

I would use a recordset as you have mentioned.
Then you will have all the data for a particular project number if there are more than one record for a project.

[cesarone82]

I would use a recordset as you have mentioned.
Then you will have all the data for a particular project number if there are more than one record for a project.

Actually I have to launch multiples dlookup to find all the fields in the records, I suppose that using recordset to retrieve all the field in the record and the using array sintax to extract them would be a better implementation, do you confirm?

[Welshgasman]

Well you can retreive more than one field in a dlookup, then split it, but you can access the recordset fields by name, so no need for an array?

[Micron]

1) is too vague to make any focused suggestions.
2)how can i reference the query directly from the click procedure passing the pjnumber as parameter?
If the query returns what you need, just use it to create a recordset based on the pjnumber by adding

WHERE tbl_projprof.pjnumber=[Forms]![frm_projects].[pjnumber] to the query. Then create the recordset and forget about DLookups. I imagine you realize that you don't need that subform you tacked on in order to do this.