Full Version: Code To Get Data Value From Query Object
UtterAccess Forums > Microsoft® Access > Access Forms
Catgold22
Hi,
I have a form with 2 x subforms, I want to auto-populate several fields in one of the subforms using an existing query.
The subform records "Drill Metres" for drill holes, the fields are:
Project, HolePrefix, HoleNo, DrillType, DrillSize, DepthFrom, DepthTo
After the "HoleNo" has been entered by the user I use the "AfterUpdate" event to run a query that looks to see if the Drillhole already exists in the database, it returns the relevant records for the Drillhole - end depths, DrillType & DrillSize. I use this query to limit the "DepthFrom" field to those entries in the database.
What I want to do now is to set the DrillType & DrillSize in the subform to the values from this same query, eg:
Me!DrillType.Value = ?DrillType field from query object "Qry_Filter_DrillPlod_HoleID_AllEntries"?
My question is how to get a field value like DrillType from the query??
any help much appreciated!
Catherine
Larry Larsen
Hi Catherine
ome thing like this may help in populating a textbox..:
CODE
Dim rs As DAO.Recordset
'/ Create your recordset..
Set rs = CurrentDb.OpenRecordset("Your SQL statement goes here")
'/ Populate your form control..
Me.TextBoxName = rs!FieldNameGoesHere
'/ Close shop..
rs.close
set rs = nothing

HTH's
Catgold22
Thanks for your reply
've tried what you've described and i get and error when it trys to execute the SQL code. I copied this code from the SQL view of the query and added the (" _ & _) so it could be seen on screen. Obviously there is something wrong with my syntax... can you assit??
'create record set
Set rs = CurrentDb.OpenRecordset("SELECT DrillMetres.To, DrillMetres.DrillType, DrillMetres.DrillSize " _
& "From DrillMetres WHERE (((DrillMetres.HolePrefix) = [Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HolePrefix])" _
& "And ((DrillMetres.HoleNo) = [Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HoleNo])) ORDER BY DrillMetres.From;")
Many thanks,
Catherine
Catgold22
Things still aren't working I get an error message "run time erro '3061', Too fee parameters, Expected 1"
This is my code
'create record set
mysql = "SELECT DrillMetres.From, DrillMetres.DrillType, DrillMetres.DrillSize"
mysql = mysql & " From DrillMetres"
mysql = mysql & " WHERE DrillMetres.HolePrefix =" & [Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HolePrefix]
mysql = mysql & " And DrillMetres.HoleNo =" & [Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HoleNo]
mysql = mysql & " GROUP BY DrillMetres.From, DrillMetres.DrillType, DrillMetres.DrillSize;"
Set rs = CurrentDb.OpenRecordset(mysql)
NB I've changed ORDER BY to GROUP BY to limit to 1 x record
Catgold22
Tried that, msg box gives me the full SQL statement + values from the form in the appropriate spots. I want records returned, not the statement...
This is so frustrating...
Catgold22
Just had a thought, "CurrentDb" as stated in code - i haven't set...
et rs = CurrentDb.OpenRecordset(mysql)
All i did was:
Dim rs As DAO.Recordset
could it be that i need to set the "currentDb"?? how to do this?
sorry for all the questions...
vtd
It sounds to me that what you have done (limiting the "DepthFrom") and what you are trying to do (setting DrillType and DrillSize) should be considered as an integral process, not 2 separate processes and I suspect that the 2 should de done in 1 "block" of code for efficiency.
Could you please describe in details the process / algorithm you used to restrict the "DepthFrom", especially the use of the Query and post relevant SQL (of the Query) and the VBA code you used?
Catgold22
Hello, another day hopefully a outcome to my code problem?!
Thanks for you interest - Ill try to better explain all that's going on in my subform and what i want out of the HoleNo_AfterUpdate event...
7 x fields for user to complete in "metres" subform - Project, HolePrefix, HoleNo, DrillType, DrillSize, (Depth)From, (Depth)To - they enter these in this order.
After the 3rd field is entered "HoleNo" I get the database to check if the hole exists in already in the database, this is done by an actual query object that is saved within the database "Qry_Filter_DrillPlod_HoleID_AllEntries". The results of this query is used as a lookup list on the "From" field so that user is restricted to enter DepthFrom from the end depths (DepthTo's) already in the database. If the hole doesn't exist in the database then the "From" field is set to 0 (all holes start from) and its disabled so the user can't change.
The SQL for this query is as follows:
SELECT DrillMetres.To, DrillMetres.DrillType, DrillMetres.DrillSize, DrillMetres.From, DrillMetres.HolePrefix, DrillMetres.HoleNo
FROM DrillMetres
WHERE (((DrillMetres.HolePrefix)=[Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HolePrefix]) AND ((DrillMetres.HoleNo)=[Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HoleNo]))
Orderther day hopefully a outcome to my code problem?!
Thanks for you interest - Ill try to better explain all that's going on in my subform and what i want out of the HoleNo_AfterUpdate event...
7 x fields for user to complete in "metres" subform - Project, HolePrefix, HoleNo, DrillType, DrillSize, (Depth)From, (Depth)To - they enter these in this order.
After the 3rd field is entered "HoleNo" I get the database to check if the hole exists in already in the database, this is done by an actual query object that is saved within the database "Qry_Filter_DrillPlod_HoleID_AllEntries". The results of this query is used as a lookup list on the "From" field so that user is restricted to enter DepthFrom from the end depths (DepthTo's) already in the database. If the hole doesn't exist in the database then the "From" field is set to 0 (all holes start from) and its disabled so the user can't change.
The SQL for this query is as follows:
SELECT DrillMetres.To, DrillMetres.DrillType, DrillMetres.DrillSize, DrillMetres.From, DrillMetres.HolePrefix, DrillMetres.HoleNo
FROM DrillMetres
WHERE (((DrillMetres.HolePrefix)=[Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HolePrefix]) AND ((DrillMetres.HoleNo)=[Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HoleNo]))
ORDER BY DrillMetres.From;
So up to this point all is working well. What i want to do in addition to the above now is auto-populate the "DrillSize" & "DrillType" of the new metres record to that already recorded for the drillhole (when the drillhole is found to exist in the database)
I thought they'd be a way to get a value from the existing query via code and set these fields. However advice from this forum is suggesting i follow the Recordset method which i've given a go but have not had success I agree with the comment that i should be able to do all these things (Limiting DepthFrom, Setting DrillType & DrillSize) in one chunk of code, not 2 as i currently have. The query way is working for what i have so far, the recordset way has not returned data as it should.
Below is all the vb code that i currently have in the HoleNo_AfterUpdate event - When this is run the an error occurs at [Set rs = CurrentDb.OpenRecordset(mysql)] - At this point i get a "Run-time error '3061', Too few parameters. Expected 1".
Private Sub HoleNo_AfterUpdate()
Dim rs As DAO.Recordset
Dim mysql As String
'Ensure "From" Field is enabled
From.Enabled = True
'Refresh query
DoCmd.Requery "From" 'this is the query "Qry_Filter_DrillPlod_HoleID_AllEntries" which gets all entries for drillhole in database (if they exist)
' its the control source for the combo box "From"
'test to see if query contains data
If DCount("*", "Qry_Filter_DrillPlod_HoleID_AllEntries") Then 'query contains records
MsgBox "Drill hole exists in DPD" & vbCrLf & vbCrLf & "Depth 'From' must be selected from list

'code to auto-populate drilltype & drillsize as per hole already in DPD
'create record set
'SQL way
mysql = "SELECT DrillMetres.DrillType"
mysql = mysql & " From DrillMetres"
mysql = mysql & " WHERE DrillMetres.HolePrefix =" & [Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HolePrefix]
mysql = mysql & " And DrillMetres.HoleNo =" & [Forms]![frmDrillPlod].[frmDrillPlod_Metres_Sub].[Form]![HoleNo]
mysql = mysql & " And DrillMetres.From = 0 "

'see the output of the sql code
MsgBox mysql

Set rs = CurrentDb.OpenRecordset(mysql)

'populate form control
Me!DrillType.Value = rs!DrillType

'close
rs.Close
Set rs = Nothing

Else
From.Value = 0
From.Enabled = False
End If
End Sub
I really hope someone can follow all of the above and shed some light as to why things aren't working
All help much appreciated !!
Catherine
Catgold22
Hello,
've copied the relevant tables, forms & queries into a new database and have attached. The main form to look at is "frmDrillPlod" and it has the 2 x subforms "frmDrillPlod_Metres_Sub" & "frmDrillPlod_Activity_Sub".
To see the error, open the main form "frmDrillPlod" and try and enter a new record - plod header data first (fields at top), then after you enter 'shift hours' and press enter - the subforms appear for data entry. Start in the meterage subform - this is where its all at! you'll get the error after you enter a HoleNo - HoleNo_AfterUpdate.
Good luck, i hope you can sort this out !!
Catgold22
ok the sql code is working now - hooray!
nother problem has arisen, i wonder if i dare ask... anyway here goes -

The idea of the code is to auto-populate when a record for the drill hole exists, but it seems to be running even when no record exists! what this means is that 'nothing' is put into the DrillType & DrillSize fields, the fields on the subforms become locked after the holeNo has been entered - whether its a new hole or not. I think this problem has something to do with the recordset not "listening" to the If statement...
Any ideas ??
Apologies for hounding the forum on my code but i am still to make things work
Catgold22
"RC" (DrillType) & "NA" (DrillSize) might be the only values in the table to date, but i'm about to start recording diamond holes "DDH" (DrillType) and "HQ3"/"NQ2"/"PQ3" (Drillsize) and the data will be different.
The problem remains - the code is not behaving as it should with respect to new Holes.
If the hole exists - get recordset, auto-populate "DrillType" & "DrillSize" with existing records. Thanks to your help this part is now working great <
BUT...
If the hole is new (does not exist in database) - allow user to select values from dropdown list of "DrillType" & "DrillSize". This is now the problem, these fields act as if they are locked and do not allow values to be picked from dropdown list....
Any ideas as to what is causing this??
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.