Loop Revisited Mrk2

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Greetings.
I have to populate a table from a table in another
database.
Reason is too produce packing slips and commercial
invoices, and the packing slip etc can vary from the
quote, so a starting point list is required.

I am using a combo box on the form to select the Quote
(Number). Everthing returned by the filter must be copied
to the new table (subform)- There can be more than one
line as the original table was a parent child.

I am playing with the following but not getting any
success.
Any ideas?.

Sub SubformControls()
'Using the selected number from the combo box
Dim strFilter As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
'Locate and open the reference table for the recordset
'and isolate the required records based on the filter
'Filter is set from the combobox on the form.

Set rs = CurrentDb.OpenRecordset("select*From[tblInventory
Tracking]where [Works No:]=" & [WorksNumber].Column(0))

rs.MoveFirst
Do Until rs.EOF

'Update the SubForm Controls from the dlookup in the
recordset
strFilter = "[Works No:]= " & [WorksNumber].Column(0)

Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup("[Invoice
Details:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))
rs.MoveNext
Loop
rs.Close

End Sub
What ever I do i get one line one record, but there can
be other results. Any help appreciated.
Kind Regards
Bill
 
Try linking to the table in the other database if you haven't already. Next,
create an Append Query that takes data from that other table and appends it
to the table in your database. Do you really need to do this or can you just
work with the linked table?

A query is much faster and easier than trying to loop through a recordset in
VBA. The query can be executed from VBA using the syntax:

CurrentDb.Execute "NameOfQuery", dbFailOnError
or
CurrentDb.Execute "SQL Statement", dbFailOnError
 
Thanks Wayne,
Input much appreciated.
Can't work with linked tables as any changes will be
common.
Need to keep an original for the iso police, hence the
need to make a copy in the new database. Query would be
fine if I can link the combobox output as the filter for
the query.
Was trying the one button approach, which was a mind set.
Regards
Bill
-----Original Message-----
Try linking to the table in the other database if you haven't already. Next,
create an Append Query that takes data from that other table and appends it
to the table in your database. Do you really need to do this or can you just
work with the linked table?

A query is much faster and easier than trying to loop through a recordset in
VBA. The query can be executed from VBA using the syntax:

CurrentDb.Execute "NameOfQuery", dbFailOnError
or
CurrentDb.Execute "SQL Statement", dbFailOnError

--
Wayne Morgan
Microsoft Access MVP


Greetings.
I have to populate a table from a table in another
database.
Reason is too produce packing slips and commercial
invoices, and the packing slip etc can vary from the
quote, so a starting point list is required.

I am using a combo box on the form to select the Quote
(Number). Everthing returned by the filter must be copied
to the new table (subform)- There can be more than one
line as the original table was a parent child.

I am playing with the following but not getting any
success.
Any ideas?.

Sub SubformControls()
'Using the selected number from the combo box
Dim strFilter As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
'Locate and open the reference table for the recordset
'and isolate the required records based on the filter
'Filter is set from the combobox on the form.

Set rs = CurrentDb.OpenRecordset("select*From [tblInventory
Tracking]where [Works No:]=" & [WorksNumber].Column(0))

rs.MoveFirst
Do Until rs.EOF

'Update the SubForm Controls from the dlookup in the
recordset
strFilter = "[Works No:]= " & [WorksNumber].Column (0)

Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup ("[Invoice
Details:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))
rs.MoveNext
Loop
rs.Close

End Sub
What ever I do i get one line one record, but there can
be other results. Any help appreciated.
Kind Regards
Bill
Click to expand...


.
Click to expand...
 
You can link the combobox as the filter. In the criteria of the query, refer
to the combobox on the form (the form has to be open for this to work,
although it can be hidden).

Forms!MyForm!MyCombobox

--
Wayne Morgan
Microsoft Access MVP


Thanks Wayne,
Input much appreciated.
Can't work with linked tables as any changes will be
common.
Need to keep an original for the iso police, hence the
need to make a copy in the new database. Query would be
fine if I can link the combobox output as the filter for
the query.
Was trying the one button approach, which was a mind set.
Regards
Bill
-----Original Message-----
Try linking to the table in the other database if you haven't already. Next,
create an Append Query that takes data from that other table and appends it
to the table in your database. Do you really need to do this or can you just
work with the linked table?

A query is much faster and easier than trying to loop through a recordset in
VBA. The query can be executed from VBA using the syntax:

CurrentDb.Execute "NameOfQuery", dbFailOnError
or
CurrentDb.Execute "SQL Statement", dbFailOnError

--
Wayne Morgan
Microsoft Access MVP


Greetings.
I have to populate a table from a table in another
database.
Reason is too produce packing slips and commercial
invoices, and the packing slip etc can vary from the
quote, so a starting point list is required.

I am using a combo box on the form to select the Quote
(Number). Everthing returned by the filter must be copied
to the new table (subform)- There can be more than one
line as the original table was a parent child.

I am playing with the following but not getting any
success.
Any ideas?.

Sub SubformControls()
'Using the selected number from the combo box
Dim strFilter As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
'Locate and open the reference table for the recordset
'and isolate the required records based on the filter
'Filter is set from the combobox on the form.

Set rs = CurrentDb.OpenRecordset("select*From [tblInventory
Tracking]where [Works No:]=" & [WorksNumber].Column(0))

rs.MoveFirst
Do Until rs.EOF

'Update the SubForm Controls from the dlookup in the
recordset
strFilter = "[Works No:]= " & [WorksNumber].Column (0)

Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup ("[Invoice
Details:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))
rs.MoveNext
Loop
rs.Close

End Sub
What ever I do i get one line one record, but there can
be other results. Any help appreciated.
Kind Regards
Bill
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Wayne,
Have the query, linked up and it runs on its own.
I tried linking the way you show but get a parametric
dialogue box, in code it errors out as undefined using
Forms!CommercialInvoice!WorksNumber.Column(0).

Playing with it and trying something else like using a
function to call the combobox -

The following is called up from a before update property
of the combo box in the open form.

'Now update the subform
CurrentDb.Execute "SubformQuery", dbFailOnError

This query has a function "WorksNo() as a criteria.

In code I have the function, which looks at the result
form the original Combobox to act as a filter,

Function WorksNo()

WorksNo = Me!WorksNumber.Column(0)
End Function

When executed the error message given is undefined
function .....in the query? And the debug sits at the line,

CurrentDb.Execute "SubformQuery", dbFailOnError

What Gives?? Where did I go wrong.
I have also followed Microsoft Access 2002 "Inside out"
and some other posts which had similar themes for criteria.
Kind regards
Bill
-----Original Message-----
You can link the combobox as the filter. In the criteria of the query, refer
to the combobox on the form (the form has to be open for this to work,
although it can be hidden).

Forms!MyForm!MyCombobox

--
Wayne Morgan
Microsoft Access MVP


Thanks Wayne,
Input much appreciated.
Can't work with linked tables as any changes will be
common.
Need to keep an original for the iso police, hence the
need to make a copy in the new database. Query would be
fine if I can link the combobox output as the filter for
the query.
Was trying the one button approach, which was a mind set.
Regards
Bill
-----Original Message-----
Try linking to the table in the other database if you haven't already. Next,
create an Append Query that takes data from that other table and appends it
to the table in your database. Do you really need to do this or can you just
work with the linked table?

A query is much faster and easier than trying to loop through a recordset in
VBA. The query can be executed from VBA using the syntax:

CurrentDb.Execute "NameOfQuery", dbFailOnError
or
CurrentDb.Execute "SQL Statement", dbFailOnError

--
Wayne Morgan
Microsoft Access MVP


Greetings.
I have to populate a table from a table in another
database.
Reason is too produce packing slips and commercial
invoices, and the packing slip etc can vary from the
quote, so a starting point list is required.

I am using a combo box on the form to select the Quote
(Number). Everthing returned by the filter must be copied
to the new table (subform)- There can be more than one
line as the original table was a parent child.

I am playing with the following but not getting any
success.
Any ideas?.

Sub SubformControls()
'Using the selected number from the combo box
Dim strFilter As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str As String
'Locate and open the reference table for the recordset
'and isolate the required records based on the filter
'Filter is set from the combobox on the form.

Set rs = CurrentDb.OpenRecordset("select*From [tblInventory
Tracking]where [Works No:]=" & [WorksNumber].Column (0))

rs.MoveFirst
Do Until rs.EOF

'Update the SubForm Controls from the dlookup in the
recordset
strFilter = "[Works No:]= " &
Click to expand...
[WorksNumber].Column
(0)
Me![SubForm subform]![Quantity] = Nz(DLookup
("[Qty:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Description] = Nz(DLookup ("[Invoice
Details:]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![UnitPrice] = Nz(DLookup ("[Price
$]", "[tblInventory Tracking]", strFilter))
Me![SubForm subform]![Total] = Nz(DLookup("[Total
Price:]", "[tblInventory Tracking]", strFilter))
rs.MoveNext
Loop
rs.Close

End Sub
What ever I do i get one line one record, but there can
be other results. Any help appreciated.
Kind Regards
Bill



.
Click to expand...
Click to expand...


.
Click to expand...
 
You say you are doing this in the BeforeUpdate of the combobox? If you want
to use the new value of the combobox, you'll need to do this in the
AfterUpdate event.

Also, you are updating the subform by running the query? To run a query it
has to be an Action Query, such as Append or Update queries. If the query
the subform is based on is has the combobox as criteria, you may simply be
able to requery the subform.

Me.NameOfSubformControl.Form.Requery

Another option is to set the Link Master field to the ComboBox (not the
combobox's field) and the Link Child field to the associated field in the
subform (these are properties of the subform control). This should cause the
subform to update automatically as you change the selection in the combobox.
The combobox's value comes from the Bound Column, if this isn't the column
you want the value from then you'll need to use the Column property as you
have indicated.
 
Back
Top