VBA to grab value of first record from subquery.

[Dranoweb]

Hi,

currently working on a few databases at present.

I have a conundrum I would like to resolve for all of them.

My initial issues of trying to use a crosstab as a subquery have been solved by just using a simple query using the summary feature provided by the query wizard.

So now, I have a subquery on a form that shows a staff name, and a tally of hours.

I would like to be able to retrieve that tally from the subquery using a button so that I can either send it to a variable, or dump it into a textbox on the parent form.

can I reference the first record and second column of a subquery the same as I could in a subform?

if so, can anyone suggest vba to do so?.

Please note I am using access 2007.

 

[vbaInet]

Hmm... you can have a query (which has a subquery) as the record source of a form but not a subquery.

You say the query is the record source of your form, if the cursor is or was on the record from which you want to retrieve that value, then you simply reference the subform followed by the control name or field name.

Me.NameOfSubformControl.txtBox1

This assumes you're calling it from the parent form.

 

[Dranoweb]

I may need to clarify a little more.

I have two tables.
for the purposes of this we will call them "Maintable" and "SummaryTable"

Maintable has a form - that has a subform (showing summarytable - one to many relationship) and also a subreport.

The subreport is a summary query of summarytable (one to one relationship)

I now notice that access has generated a subform for me, in a tabular layout for the query. I should have expected that.

anyway I have tried your code, and attempted to get it to display in a messagebox for testing. my code was as follows:

Dim message As String
message = Me.leavetakenQuerysubform.SumOfHours
MsgBox message

Resulting in the error: Method or data member not found.

"leavetakenQuerysubform" is the subform name
"SumOfHours" is the control name from which I require the value.

 

[vbaInet]

Ok, there's the subform name and there's also the subform control name. Click on the "subform" once and look in the name property. That will be the control and from that you can get the name. If you click on it a second time, it will take you to the subform.

So what you want is the name of the subform control, not the name of the subform.

 

[Dranoweb]

Got it working:

Dim message As String
message = Me!leavetakenQuerysubform.Form!SumOfHours.Value
MsgBox message

This page was very useful:

http://www.mvps.org/access/forms/frm0031.htm

 

[vbaInet]

Great!!

Fyi: it's much better to write that code like this

MsgBox Me!leavetakenQuerysubform.Form!SumOfHours.Value

That's two less lines for the compiler to parse.

 

[Dranoweb]

Just an addition, ran into an error when the subform had no records, so I added an error handler like so:

Dim Tally As String
If Me!leavetakenQuerysubform.Form.Recordset.RecordCount = 0 Then
Else
Tally = Me!leavetakenQuerysubform.Form!SumOfHours.Value
leavedays.Value = Tally
End If

 

[vbaInet]

Use RecordsetClone instead of Recordset.

It looks like you're used to assigning your values into a variable before passing it to a control? That's unecessary extra work for the compiler. Just write it in one line if it fits.

 

[Dranoweb]

Use RecordsetClone instead of Recordset.

It looks like you're used to assigning your values into a variable before passing it to a control? That's unecessary extra work for the compiler. Just write it in one line if it fits.

I will be doing other things with the variable further down the code.

this is kind of mashing two steps together in preparation for some juggling further along.

For instance I have a few labels that I want to add the string into, EG:

label1.caption = staffname & " has taken " & tally & " hours in leave this year!"

and:

label2.caption = staffname & " has " & (tally /8) & " days accrued to his/her name"

 

[vbaInet]

Alright, I thought that was just your way of writing.

Why not use a textbox so you can take advantage of the Control Source property? You can make the textbox look like a label by setting its Enabled property to No and its Locked property to Yes.

 

[Dranoweb]

Alright, I thought that was just your way of writing.

Why not use a textbox so you can take advantage of the Control Source property? You can make the textbox look like a label by setting its Enabled property to No and its Locked property to Yes.

Labels have advantages of their own, they also behave differently on reports.

much of my code will be cut and pasted to a report at some stage, and I like to minimize my work in modifying procedures.

It's difficult to explain everything I'm working, as it seems to have sparked your interest.

I may have to organize a copy of my biggest current project for you through a PM or something.

You may begin to get the picture then.

 

[vbaInet]

I may have to organize a copy of my biggest current project for you through a PM or something.

You won't need to do that. I don't think I will have the time to look at it

I was just making suggestions as the conversation progressed.

Good luck with the projects.

 

[Dranoweb]

Not a prob, Yeah I have difficulty finding time to design it myself. it's 23mb when compacted now, and there is just a few test records in there.

could also be the bound OLE objects in there too.

 

[vbaInet]

It could well be. There's a cleaner approach for displaying images? Instead of using the OLE field, you save the full path to the image in a text field and set that field as the control source of an Image Control. There's more about this on here.

 

[Dranoweb]

No not for images, for varied types of documents that must be associated with a job record. These could be anything from PDF to xls.

There really isnt any other way to get around the requirement.
In case I am unclear, i was referring to an OLE type column in a table.

 

[vbaInet]

I see. Look into Application.FollowHyperlink

 

[Dranoweb]

No, linking won't work. The database will be on a server. Files must stay with the database. For both location purposes and due to privacy regulations. I am making reference to parts of 3 different databases here, but the one that requires the OLE field if for a government institution.

I am familiar with the hyperlink reference - as would be evident from this post of mine:
http://www.access-programmers.co.uk/forums/showthread.php?t=202961

 

[vbaInet]

Quite well versed then.

I will let you carry on. Happy coding!

 

[Dranoweb]

Thanks - owe you one. Drop me a line if you ever need assistance with circuit design, model rocketry, firearms, laser engraving or pretty much anything else that's dangerous and unusual.

 

[vbaInet]

Thanks - owe you one. Drop me a line if you ever need assistance with circuit design, model rocketry, firearms, laser engraving or pretty much anything else that's dangerous and unusual.

"anything else that's dangerous", I like that I shall let you know when I'm in dire need