filling fields with query results

[Brian]

Hello,

Basically, I'm running a query on a form's activation, and I'd like to
have the results of the query be placed into other fields on the same
form automatically.

Does anybody know how this can be done?

I've tried setting the default value of the text fields on the form to
be equal to [NameOfQuery]![FieldOfQuery] using Access' expression
builder. This doesn't seem to work, always leaving the text fields
blank, even though the query results are accurate.

Any ideas?

Thanks,

Brian

[Salad]

Brian wrote:

Hello,

Basically, I'm running a query on a form's activation, and I'd like to
have the results of the query be placed into other fields on the same
form automatically.

Does anybody know how this can be done?

I've tried setting the default value of the text fields on the form to
be equal to [NameOfQuery]![FieldOfQuery] using Access' expression
builder. This doesn't seem to work, always leaving the text fields
blank, even though the query results are accurate.

Any ideas?

Thanks,

Brian

Open the text box's property sheet, click on the Data tab, select
Control Source, and from the dropdown in that row select the field you
want filled in.

If that is not what you mean, you may want to open the query as a
recordset (I'll assume it is one record) and update the fields from the set.

Dim rst As Recordset
set rst = Currentdb.Openrecordset("QueryName",dbopensnapshot )
Me.FieldNameToFill = rst!TheFieldData
rst.close
set rst = Nothing

[Brian]

Yes. That's right. This query results in one record.

I tried your code as shown:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("PO Sub Total Query",
dbOpenSnapshot)
Me.SubTotal = rst!SubTotal
rst.Close
Set rst = Nothing
where the name of my query is "PO Sub Total Query", the field I'm
trying to fill automatically is "SubTotal" and the field generated by
the query (which is an expression that sums up all PurchasePrice fields
of each record) is "SubTotal".

I get this error:

"Too few parameters. Expected 1."

Does anybody know what parameter is required? Sorry about all this. I
know Access pretty well minus the VB stuff.

Thanks!

Brian

[Fred Zuckerman]

"Brian" <bc****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Yes. That's right. This query results in one record.

I tried your code as shown:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("PO Sub Total Query",
dbOpenSnapshot)
Me.SubTotal = rst!SubTotal
rst.Close
Set rst = Nothing
where the name of my query is "PO Sub Total Query", the field I'm
trying to fill automatically is "SubTotal" and the field generated by
the query (which is an expression that sums up all PurchasePrice fields
of each record) is "SubTotal".

I get this error:

"Too few parameters. Expected 1."

Does anybody know what parameter is required? Sorry about all this. I
know Access pretty well minus the VB stuff.

Thanks!

Brian

Just guessing, but you might try adding the following stmt
rst.MoveFirst
before the stmt: Me.SubTotal = rst!SubTotal

I haven't read this thread from the beginning,
but which form event are you using for this code?
Fred Zuckerman

[Salad]

Brian wrote:

Yes. That's right. This query results in one record.

I tried your code as shown:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("PO Sub Total Query",
dbOpenSnapshot)
Me.SubTotal = rst!SubTotal
rst.Close
Set rst = Nothing
where the name of my query is "PO Sub Total Query", the field I'm
trying to fill automatically is "SubTotal" and the field generated by
the query (which is an expression that sums up all PurchasePrice fields
of each record) is "SubTotal".

I get this error:

"Too few parameters. Expected 1."

Does anybody know what parameter is required? Sorry about all this. I
know Access pretty well minus the VB stuff.

Thanks!

Brian

I would open up "PO Sub Total Query". Look for anything in the Criteria
row(s) that has a parameter...ex Forms!Mainform!Name...where the
criteria expects the name from a form...perhaps a date range...who
knows. If not there, it may be in another query "PO Sub Total Query" uses.

Maybe you can make the query more generic. Perhaps you need to look at
Parameters in on-line help for more detail.

[Brian]

Hello,

Damn you guys are good. Got it right on the button. In my query, one
criteria field contains [Forms]![Purchase Orders]![PurchaseOrderID],
which will isolate all assets in my database that contain
PurchaseOrderID's identical to the currently opened Purchase Order
form (i.e. to find the assets of the current PO). Currently, this is
the code that I have:
Private Sub RefreshPOForm_Click()
On Error GoTo Err_RefreshPOForm_Click

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("PO Sub Total Query",
dbOpenSnapshot)
Me.SubTotal = rst!SubTotal
rst.Close
Set rst = Nothing

Me.Refresh

Exit_RefreshPOForm_Click:
Exit Sub

Err_RefreshPOForm_Click:
MsgBox Err.Description
Resume Exit_RefreshPOForm_Click
End Sub
Basically I have a command button on the PO form that is dedicated to
refreshing the form (hence the Me.Refresh). I'd also like to update
all the fields on the form every time this button is pressed, hence me
wanting to auto fill fields based on the results of a query

I've only done Access for a few weeks now, and I pretty much
understand what is going on (in terms of ms access wizards...lol),
minus the VB coding aspect. Would you guys be able to help me out?

How would I go about setting up the query and its criteria field to
only obtain assets with a puchaseOrderID identical to the open Purchase
Order form's PurchaseOrderID, and be able to get the above code to
work?

Also, in terms of VB coding, would scope be an issue? Can the function
"Openrecordset" be able to access the query? Are queries global
objects?

Thanks again guys!

Brian

[Salad]

Brian wrote:

Hello,

Damn you guys are good. Got it right on the button. In my query, one
criteria field contains [Forms]![Purchase Orders]![PurchaseOrderID],
which will isolate all assets in my database that contain
PurchaseOrderID's identical to the currently opened Purchase Order
form (i.e. to find the assets of the current PO). Currently, this is
the code that I have:
Private Sub RefreshPOForm_Click()
On Error GoTo Err_RefreshPOForm_Click

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("PO Sub Total Query",
dbOpenSnapshot)
Me.SubTotal = rst!SubTotal
rst.Close
Set rst = Nothing

Me.Refresh

Exit_RefreshPOForm_Click:
Exit Sub

Err_RefreshPOForm_Click:
MsgBox Err.Description
Resume Exit_RefreshPOForm_Click
End Sub
Basically I have a command button on the PO form that is dedicated to
refreshing the form (hence the Me.Refresh). I'd also like to update
all the fields on the form every time this button is pressed, hence me
wanting to auto fill fields based on the results of a query

I've only done Access for a few weeks now, and I pretty much
understand what is going on (in terms of ms access wizards...lol),
minus the VB coding aspect. Would you guys be able to help me out?

How would I go about setting up the query and its criteria field to
only obtain assets with a puchaseOrderID identical to the open Purchase
Order form's PurchaseOrderID, and be able to get the above code to
work?

Also, in terms of VB coding, would scope be an issue? Can the function
"Openrecordset" be able to access the query? Are queries global
objects?

Thanks again guys!

Brian

Brian, oftentimes it is best to "select" the whole recordset ...with
some judicious filter conditions. For example, I may want to select all
records that are active, never those that are flagged inactive. So I
might have a query that selects the records and the required fields for
the query only for active records...but leave the filtering of records
for later...where I might want to filter records based on a name or a
date range or a department, etc.

So your "PO Sub Total Query" might do all of the summing and grouping.
But the filtering can take place later. For example, you could open a
form like this
Docmd.Openform "FormName",,,"PONum = 123"
and this will filter the recordset and only display the record for
PONumber = 123

You can also filter the recordset with anothe SQL. ExL
Dim rst As Recordset
Dim strSQL As String
strSQL = "Select * From [PO Sub Total Query] Where PONum = 123"
set rst = currentdb.openrecordset(strSQL,dbopensnapshot)
...do what ever you want for all records where PONum is 123
rst.close
set rst = Nothing

Queries are not, per-se, global objects...they are database
objects...just as tables, forms, reports, macros, and modules are. They
are more similar to tables. You can have a table called Employees. If
you do, you can't have a query called Employees...it must have a
different name.

This is a side note...I would HIGHLY recommend you not use spaces in
table, query, form, and report names. What many programmers do is
capitalize the first character of a word. For Example, PO Sub Total
Query would be called POSubTotalQuery. Having spaces in names works,
but it adds additional responsibilities to the programmer. You need to
put [] brackets around the names which adds more typing to your code.

The times I may use a space in a query is when I wish to present column
headings in a listbox or combo box. Let's say the field is CustNum, but
in the row heading column of a list box or combo box I want to see
Customer #. In my rowsource for that combo/list box I might enter
Customer # : CustNum

This is just a recommendation...you can follow or ignore. Over time, I
think you'll find it good advice.

Also, some people add tbl in front of table names, qry in front of query
names, frm in front of form names. Again, it's fine for some but I find
it a PITA. When I work in the database window, I want to see things in
alphabetical order without 3 extra chaacters in front of the
name...those 3 characters just get in the way...IMHO.

However, I strongly recommend you used int in front of integers, str in
front of strings, lng in front of longs, dat in front of dates...etc
inside your code. Ex:
Dim strSQL As String
Dim intFlag as Integer
The reason for this is that you always know what type of variable you
are working with.