Extract field from query results into VBA variable (1 Viewer)

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
Hello all,
I am trying to make a VBA script that will be in the 'AfterUpdate' event of a textbox in a form that needs to get the value from the output of a query and use it further down the code.
So i have a query (which works as expected) where the output fields are: PartID, ManufID, ManufPN.

Here is the SQL:
SELECT qryPartsToStock.PartID, qryPartsToStock.ManufID, qryPartsToStock.ManufPN
FROM qryPartsToStock
WHERE (((qryPartsToStock.ManufID)=[Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerID]) AND ((qryPartsToStock.ManufPN)=[Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerPN]));

I want to get the PartID field from the query results (query should only ever output one entry, or no entries but i should be able to handle that without too much trouble)

After much browsing online i keep hitting dead-ends...

Any suggesting on how i can get the PartID from the Query into a variable in my script?

Thankyou for any advice
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:45
Joined
May 7, 2009
Messages
19,169
You can directly use Dlookup() to return thr partid.
 

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
You can directly use Dlookup() to return thr partid.

Hi arnelgp,

i have tried that but get the following error:

"Run-time error '2465': Microsoft Access can't find the field '|1' referred to in your expression"

i have no idea what the '|1' referenced in the error comes from...


This is the code i have at the moment as a starting point:

Private Sub comboManufacturerPN_AfterUpdate()
Dim SetPartID As Long
SetPartID = DLookup([PartID], "qryStockFindMatchingID")
End Sub

i did not put any criteria in the DLookup because the query already filters exactly what i need so i just need the PartID that is the single result of that query...
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:45
Joined
Jan 23, 2006
Messages
15,364
What is the SQL for qryPartsToStock ? It is often helpful to supply a graphic of your table relationships and/or a copy of your database. Good luck with your project.
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:45
Joined
Aug 11, 2003
Messages
11,696
Code:
dim rs as dao.recordset
set rs = currentdb.openrecordset("yourquery")
Debug.print rs!PartID
msgbox rs!PartID
rs.close
set rs = nothing
Something along those lines?
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:45
Joined
Aug 11, 2003
Messages
11,696
another solution could be....

Code:
debug.print DLookup("PartID", "qryPartsToStock", "qryPartsToStock.ManufID=" & [Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerID]"& _ 
                                            " AND qryPartsToStock.ManufPN=" & [Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerPN]

Though using Dlookups is generaly a plague that you should try and avoid.
 

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
Code:
dim rs as dao.recordset
set rs = currentdb.openrecordset("yourquery")
Debug.print rs!PartID
msgbox rs!PartID
rs.close
set rs = nothing
Something along those lines?

Thanks for the suggestion Namliam

This seems to work OK if i use a table (instead of "yourquery"), but when i put in the name of my query i get the following error "Too few parameters. Expected 2"
Just for troubleshooting i tried with another query and i get the following error: "Too few parameters. Expected 1"

Not much luck on my google searching.... any ideas what the problem could be?
 

Gasman

Enthusiastic Amateur
Local time
Today, 23:45
Joined
Sep 21, 2011
Messages
14,048
Your queries expect parameters?
 

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
Your queries expect parameters?

i guess that the 2 parameters could be the the sections bold below from the SQL of the qryStockFindMatchingID query?
(the other query i tested with that had the error expecting 1 parameter has 1 'referenced filter' (for lack of a better word) so i guess that would make sense)

SELECT qryPartsToStock.PartID, qryPartsToStock.ManufID, qryPartsToStock.ManufPN
FROM qryPartsToStock
WHERE (((qryPartsToStock.ManufID)=[Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerID]) AND ((qryPartsToStock.ManufPN)=[Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerPN]));


The frmPOItems from the bolded items above is actually the same form where i am running the script i am trying to work, so i could access the comboManufacturerID and comboManufacturerPN values directly.

This is the code i have at the moment

Code:
Dim rs As dao.Recordset

Set rs = CurrentDb.OpenRecordset("qryStockFindMatchingID")
Debug.Print rs![PartID]
MsgBox rs![PartID]
rs.Close
Set rs = Nothing

So i guess i would have to add parameters to the "Set rs = CurrentDb.OpenRecordset" line?...
... cannot work out how to do that......
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:45
Joined
Aug 11, 2003
Messages
11,696
Your query requires your form to be open, assuming it is open your query should work....

have you tried the dlookup solution?

Code:
debug.print DLookup("PartID", "qryPartsToStock", "qryPartsToStock.ManufID=" & [Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerID]"& _
" AND qryPartsToStock.ManufPN=" & [Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerPN]

Also you could try using only
Code:
debug.print [Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerID]
debug.print [Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerPN]
to see if your form can be found.... or use msgbox instead of the debug.print.

btw assuming your comboManufacturerPN is a cascading combobox from the ManufacturerID you can add your PartID as a hidden column in the combobox and you can retrieve the value directly from said hidden column.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:45
Joined
May 7, 2009
Messages
19,169
Code:
DoCmd.SetParameter "[Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerID]", Me!comboManufacturerID
DoCmd.SetParameter "[Forms]![frmPONumbers]![frmPOItems].[Form]![comboManufacturerPN]", Me!comboManufacturerPN
Dim partid As String
partid = DLookup("PartID", "qryPartsToStock") & ""
MsgBox partid
 

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
Hello all,
thankyou to all the suggestions, i think several would get me there, but in the meantime i found a very simple workaround that works perfectly for me:

I made a hidden combobox called comboMatchingID that has my query as the row source, then i use the following code to get the PartID out of it:

Code:
Dim SetPartID As Long
Me.comboMatchingID.Requery 'requery to make sure results are up to date
Me.comboMatchingID = Me.comboMatchingID.ItemData(0) 'select first item in the list
SetPartID = Nz(comboMatchingID) 'get PartID (it is the bound column of the combobox), in my case i want it to be set to zero if there are no matching PartIDs so i used the nz() function

A bit rudimentary, but works exactly as i want it to and i have the full flexibility to change the query directly from the query builder if i need, and i can easily get data from the other columns of the combobox without too much trouble if i even need to.
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:45
Joined
Aug 11, 2003
Messages
11,696
Why a hidden combobox, why not simply add your PartID to your existing combobox as hidden column and fetch it from there ? As per my earlier suggestion?
 

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
Why a hidden combobox, why not simply add your PartID to your existing combobox as hidden column and fetch it from there ? As per my earlier suggestion?

My objective here is to have the user be able to:
- select a PartID from a combobox (this then runs an AfterUpdate event that autofills the ManufacturerID and ManufacturerPN)
OR
- Select the manufacturer form a combobox and enter a ManufacturerPN in a textbox and if I happen to already have a PartID that matches the ManufacturerID+ManufacturerPN combination fill in the PartID field correspondingly. This is for a general materials entry form and a significant amount of the time the Manufacturer and ManufacturerPN will be entered without it needing to be fully categorized in the database with a matching PartID.
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:45
Joined
Aug 11, 2003
Messages
11,696
This is what a cascading combobox is for, this should also allow you to add a new record if it is not in the list....
If you use a combobox instead of a textbox it is faster, less error prown (users can see both NR and Description for example) and faster since you can hide the ID and fetch it straight from there. win-win-win it would seem to me ?

But hey if you have it working, it works even if its not the most obvious or direct route.
 

dlambert

Member
Local time
Tomorrow, 00:45
Joined
Apr 16, 2020
Messages
42
This is what a cascading combobox is for, this should also allow you to add a new record if it is not in the list....
If you use a combobox instead of a textbox it is faster, less error prown (users can see both NR and Description for example) and faster since you can hide the ID and fetch it straight from there. win-win-win it would seem to me ?

But hey if you have it working, it works even if its not the most obvious or direct route.

I am quite new to the whole Access world, this is my first project in Access (though i have previous programming experience), so i clearly do not know all the the 'industry standard' ways of doing certain things, and sometimes end up with unusual workarounds... but as you said this one works OK for my application at the moment.

I have changed my textbox for the ManufacturerPN to a combobox that shows me a list of previously entered PartNumbers for that manufacturer but allows new ones to be entered, but i do not want that to be added to my product list (with a PartID etc) because a lot of these entries will be one offs that i will never use again, and for the parts that i do want to add to my products list I prefer the user to use a different form i have created that makes sure that all description fields are filled correctly and consistently with the previously already entered parts.
 

namliam

The Mailman - AWF VIP
Local time
Tomorrow, 00:45
Joined
Aug 11, 2003
Messages
11,696
It has been a while but IIRC there is an option to allow data entry or not in the properties as well as a "not in list" option what to do with the data not existing (ie. open a form to add the new part)

In a relational environment any part used should have an ID, period... you can then proceed to hide them if they are "one off" by ticking a hide box or something to exclude them from the "short" list that are repeated products. Not having IDs breaks your entire database or at the very least makes it a flawed design.
 

Users who are viewing this thread

Top Bottom