Multiply qty of filtered records

[Robert5833]

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[dymondjack]

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[dymondjack]

Correction... you need to move the first record before you start the loop,
after you make sure there is a record:

....

If rs.RecordCount <> 0 Then

rs.MoveFirst '<---- ADD THIS LINE

'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty

....


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[Robert5833]

Hello Jack,

Thank you for the reply(s); this looks like what I need. I'll try it out and
report back!

Best regards,
Robert

Correction... you need to move the first record before you start the loop,
after you make sure there is a record:

...

If rs.RecordCount <> 0 Then

rs.MoveFirst '<---- ADD THIS LINE

'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty

...


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[dymondjack]

For the record, this can be done much easier using a sum query based of the
same records as the loop I suggested, but I'm so terrible with queries its
not even funny.

Many someone else can give you the specifics.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack,

Thank you for the reply(s); this looks like what I need. I'll try it out and
report back!

Best regards,
Robert

Correction... you need to move the first record before you start the loop,
after you make sure there is a record:

...

If rs.RecordCount <> 0 Then

rs.MoveFirst '<---- ADD THIS LINE

'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty

...


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[Robert5833]

Hello Jack,

This is a start anyway, and I've found that having a starting point *and
what you have offered up is a good one* leads to a full solution at some
point (or in my case, at least enough of an understanding to know if I’m on
the right track!).

Many thanks! I appreciate your help!

Best regards,
Robert

For the record, this can be done much easier using a sum query based of the
same records as the loop I suggested, but I'm so terrible with queries its
not even funny.

Many someone else can give you the specifics.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack,

Thank you for the reply(s); this looks like what I need. I'll try it out and
report back!

Best regards,
Robert

Correction... you need to move the first record before you start the loop,
after you make sure there is a record:

...
If rs.RecordCount <> 0 Then
rs.MoveFirst '<---- ADD THIS LINE
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
...


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[dymondjack]

If you should want to try doing this with a query (which is probably the more
correct way), open your query builder in design view and build your base
information (or switch to SQL view and copy/paste your sql statement from the
immediate window as Tom suggested in an earlier post).

At this point you should see all your field names and the criteria, sort
order, etc in the bottom part of the design view.

From here, you can create calculated fields. Go to the first empty area,
and where you usually would see your field name. Enter something along the
following lines to create a calculated field:

QtyReqd: [qtyperassyfield]*[partqty]

This should make a calculated field called QtyReqd, which is equal to your
existing field [qtyperassyfield] times exisiting field [partqty]

If you want to replace one of those with a function (the function needs to
be in a standard module not a form module, and declared as a Public Function)
with a field value as an argument, do it like so:

QtyReqd: YourFunction([fieldname])

You can use this function as a value in mathmatical expressions as well:

QtyReqd: [fieldname]*YourFunction([fieldname])



As far as coming up with a sum value of all your records, I'm still trying
to figure that out. I haven't been able to get this information in datasheet
view. I do know how to get this information in a Report, and I *think* you
can do the same in the footer of a Form.

Make an unbound control and set the control source to:

=Sum([fieldname])

If this is in the footer of a report, it adds the values of each record in
the report and displays the total. I'm not positive about doing this with
forms, but I think you can with a continuous form. I do know it works with
reports though.

I would assume there is a way to do this through code as well (referring to
the sql itself rather than a control), but I'm not sure. As I mentioned, I'm
terrible with queries, though I do intend to fix this asap.

If anyone knows how to get that total, I would be greatly appreciative.
I've played around with the Totals in the query design view but haven't been
able to come up with much.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack,

This is a start anyway, and I've found that having a starting point *and
what you have offered up is a good one* leads to a full solution at some
point (or in my case, at least enough of an understanding to know if I’m on
the right track!).

Many thanks! I appreciate your help!

Best regards,
Robert

For the record, this can be done much easier using a sum query based of the
same records as the loop I suggested, but I'm so terrible with queries its
not even funny.

Many someone else can give you the specifics.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack,

Thank you for the reply(s); this looks like what I need. I'll try it out and
report back!

Best regards,
Robert

:

Correction... you need to move the first record before you start the loop,
after you make sure there is a record:

...
If rs.RecordCount <> 0 Then
rs.MoveFirst '<---- ADD THIS LINE
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
...


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL

 

[Robert5833]

Hello Jack,

And thank you for yet another great reply, and the huge help you've provided
in answering my questions. I’ll review this information and guidance and see
how well I’m able to apply it. At first blush it looks to be a workable
solution.

My comments in line below:

If you should want to try doing this with a query (which is probably the more
correct way), open your query builder in design view and build your base
information (or switch to SQL view and copy/paste your sql statement from the
immediate window as Tom suggested in an earlier post).

I’ve tried to do this and have had some success. It’s an easy enough action,
but my experience using the Immediate Window is limited, but gaining
momentum. Thanks!

At this point you should see all your field names and the criteria, sort
order, etc in the bottom part of the design view.

From here, you can create calculated fields. Go to the first empty area,
and where you usually would see your field name. Enter something along the
following lines to create a calculated field:

QtyReqd: [qtyperassyfield]*[partqty]

I did this, and then copied the SQL string from the SQL view in the query
object, and pasted it into the Record Source property of the form. Now I have
a bound control on that form that shows me the total qty required. Thank you!

This should make a calculated field called QtyReqd, which is equal to your
existing field [qtyperassyfield] times exisiting field [partqty]

If you want to replace one of those with a function (the function needs to
be in a standard module not a form module, and declared as a Public Function)
with a field value as an argument, do it like so:

QtyReqd: YourFunction([fieldname])

You can use this function as a value in mathmatical expressions as well:

QtyReqd: [fieldname]*YourFunction([fieldname])

I’m not sure I follow this bit on using a Function clearly enough; and
thought I’d take a crack at drafting up a sample function and call in a sub
to demonstrate what I think I understand…but after two hours of reading back
through what you’ve provided me so far, and a bunch of other threads; while I
have a suspicion the function approach is better somehow I need to gain a
better footing on that whole process before investing more time right now.
That said; for the time being I’ll stay with query solution you’ve provided.

As far as coming up with a sum value of all your records, I'm still trying
to figure that out. I haven't been able to get this information in datasheet
view. I do know how to get this information in a Report, and I *think* you
can do the same in the footer of a Form.

Make an unbound control and set the control source to:

=Sum([fieldname])

If this is in the footer of a report, it adds the values of each record in
the report and displays the total. I'm not positive about doing this with
forms, but I think you can with a continuous form. I do know it works with
reports though.

I have created the query you suggested and some additional text boxes on my
form, and I’m able to display the totals as I would have hoped. But as you’ve
indicated here, the question is still open as to how a recordset can be
created as a function of the *total required* number of items.

Could it be that a clone of the initial recordset could be created, and
another clone created for the multiple of each line item whose total quantity
is greater than one?

In the mean time, I’ll continue to research that point; maybe using similar
examples of creating multiple instances of data, objects, etc.

I would assume there is a way to do this through code as well (referring to
the sql itself rather than a control), but I'm not sure. As I mentioned, I'm
terrible with queries, though I do intend to fix this asap.

If anyone knows how to get that total, I would be greatly appreciative.
I've played around with the Totals in the query design view but haven't been
able to come up with much.

As always; I thank you for the help, and I appreciate the time you’ve spent
thus far.

Best regards,
Robert

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

Hello Jack,

This is a start anyway, and I've found that having a starting point *and
what you have offered up is a good one* leads to a full solution at some
point (or in my case, at least enough of an understanding to know if I’m on
the right track!).

Many thanks! I appreciate your help!

Best regards,
Robert

For the record, this can be done much easier using a sum query based of the
same records as the loop I suggested, but I'm so terrible with queries its
not even funny.

Many someone else can give you the specifics.

--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Hello Jack,

Thank you for the reply(s); this looks like what I need. I'll try it out and
report back!

Best regards,
Robert

:

Correction... you need to move the first record before you start the loop,
after you make sure there is a record:

...
If rs.RecordCount <> 0 Then
rs.MoveFirst '<---- ADD THIS LINE
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
...


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Run a recordset loop and counter.... each record you go through can add to
the counter.


Private Function TotalQty() As Long
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngReqdQty As Long
Dim lngTotal As Long

strSQL = 'the records to tally

Set rs = Currentdb.OpenRecordset(strSQL)

'Make Sure there's at least one record
If rs.RecordCount <> 0 Then
'Loop the recordset, adding the quantity each time
While rs.EOF = False '(EOF is End Of File)
lngRedqQty = rs.Fields("basequantityfieldname")
lngTotal = lngTotal + lngReqsQty
rs.MoveNext
Wend
End If

rs.Close
Set rs = Nothing 'don't forget these to lines

TotalQty = lngTotal
End Function


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain


:

Hi all;

The more I learn…the more I learn how much I have yet to learn…

Vista Business, Access 2007; computer is fast with tons of RAM

I have a slight dilemma; I have a form that I use to filter records in one
table (call them requirements) based on their relationship to a unique item
in another table (an item’s model number).

This information (the form’s recordset) is then appended to another table as
a list of requirements used for tracking purposes.

From the table the requirements are taken from, I have a field that stores a
value for the quantity required to make up a complete assembly; nothing
special there.

My dilemma is that the appended recordset needs be a sum total of those
records which have a multiple greater than one (1). The table to which the
data (recordset) is appended allows duplicates, so that’s not a problem.

The problem is that I don’t know how to build a procedure or function that
will either pass the results into my INSERT INTO function, or to perhaps to
call such a function from within the INSERT INTO function.

Here’s my INSERT INTO procedure:

Public Function loadMe()
On Error GoTo Error_Handler

Dim dbs As DAO.Database
Set dbs = CurrentDb

CurrentDb.Execute "INSERT INTO tblTracked([fldTrackedID], " _
& "[fldTrackedMake], [fldTrackedModel], [fldTrackedPN]) " & _
"SELECT fldTrackedID, fldTrackedMake, fldTrackedModel, " & _
"fldTrackedPN " & _
"FROM tblTrackReqd " & _
"WHERE tblTrackReqd.fldTrackedID = " &
[Forms]![frmBuildTracked]![cboSelTrackedModel], dbFailOnError

Exit_Procedure:
On Error Resume Next
Set dbs = Nothing
Exit Function
Error_Handler:
DisplayUnexpectedError Err.Number, Err.Description
Resume Exit_Procedure
Resume

End Sub

The above procedure is compiled, and doesn’t throw any errors (and it works
too!).

Meanwhile I’ve created a Function to return the value (quantity) required
for each complete assembly, but I think it’s only able to return one
instance, rather than a looped (?) collection of the result for the entire
recordset. (I’ve received some good tutoring on Functions and Subs from folks
here in these Discussion Groups, but I’m nowhere close to being able to
build/use them effectively.)

The alternative I suppose is to list each item in the requirements table by
the multiple required for each assembly. But that’s kind of cheesy, and I
know the best way to do it for the long-term is through automation.

I just wish I knew how… <frown>

If anyone has a good idea or two on how I might be able to do this, or if
what I’ve described that I think I want to do sounds a bit like pushing rope
up a hill <smile>; well I’d appreciate being educated on that too.

Thank you in advance for any help and advice.

Best regards,

RL