Query and Module

[bmoyer123]

Is it possible to run a Module in a Query? I have a module that adds data to a table using a form, but this only works on one entry at a time. I have an entire table with data (over 4000 records) that needs to me converted using the same criteria as written into the module, but I don't know how to do this? I can select the data that needs to be changed using a query, but don't know how to proceed.

[HansV]

Welcome to Eileen's Lounge!

It is not possible to run a module from a query, but it is possible to write code that opens a recordset on a query, and processes the records one by one. Or perhaps you could use an append query.

If you provide some details about your code, we may be able to provide more specific help.

[bmoyer123]

The code is as follows:

Option Compare Database

Dim dbs As DAO.Database ' variable for recordset
Dim rsDivisions As DAO.Recordset
Dim rsNewProducts As DAO.Recordset
Dim rsAddProducts As DAO.Recordset
Dim strSQL As String ' variable for string expressions
Dim strWSQL As String
Sub WriteDivisions()
Set dbs = CurrentDb
Set rsAddProducts = dbs.OpenRecordset("tblInventory") ' open the table
rsAddProducts.MoveLast

For numdiv = 1 To Form_frmAddDivisions.Division
rsAddProducts.AddNew
rsAddProducts("COLL#") = Form_frmAddDivisions.ProductID
If numdiv > 26 Then
rsAddProducts("BAGID") = Chr(numdiv - 26 + 64) & Chr(97)
Else
rsAddProducts("BAGID") = Chr(numdiv + 64) & "0"
End If
rsAddProducts("ProdCode") = Form_frmAddDivisions.ProdCode
rsAddProducts("VOLBAG") = Form_frmAddDivisions.Volume
rsAddProducts("BAGSFRZ") = Form_frmAddDivisions.Division
rsAddProducts("StorageUnitID") = Form_frmAddDivisions.Storage
rsAddProducts("SectionID") = Form_frmAddDivisions.SectionID
rsAddProducts("RackID") = Form_frmAddDivisions.Rack
rsAddProducts("StatusID") = Form_frmAddDivisions.Status
rsAddProducts("TECHID") = Form_frmAddDivisions.Tech
rsAddProducts.Update
Next numdiv
End Sub

[HansV]

If you have a query that returns all the records that you'd want to load into frmAddDivisions and then process in your code, you could open a recordset on that query and loop through its records. You can then refer to the fields in the query instead of to the controls on the form:

Code: Select all

Sub WriteDivisions()
    Dim numdiv As Long
    Dim rsQuery As DAO.Recordset
    Set dbs = CurrentDb
    Set rsQuery = dbs.OpenRecordset("MyQuery", dbOpenForwardOnly)
    Set rsAddProducts = dbs.OpenRecordset("tblInventory") ' open the table
    rsAddProducts.MoveLast

    Do While Not rsQuery.EOF
        For numdiv = 1 To rsQuery!Division
            rsAddProducts.AddNew
            rsAddProducts("COLL#") = rsQuery!ProductID
            If numdiv > 26 Then
                rsAddProducts("BAGID") = Chr(numdiv - 26 + 64) & Chr(97)
            Else
                rsAddProducts("BAGID") = Chr(numdiv + 64) & "0"
            End If
            rsAddProducts("ProdCode") = rsQuery!ProdCode
            rsAddProducts("VOLBAG") = rsQuery!Volume
            rsAddProducts("BAGSFRZ") = rsQuery!Division
            rsAddProducts("StorageUnitID") = rsQuery!Storage
            rsAddProducts("SectionID") = rsQuery!SectionID
            rsAddProducts("RackID") = rsQuery!Rack
            rsAddProducts("StatusID") = rsQuery!Status
            rsAddProducts("TECHID") = rsQuery!Tech
            rsAddProducts.Update
        Next numdiv
        rsQuery.MoveNext
    Loop
    rsQuery.Close
    rsAddProducts.Close
End Sub

[bmoyer123]

Currently my form condsists of unbound fields. When command button is pressed, the module runs and adds records to table.

Would I link all unbound fields on form to data fields in query.

[HansV]

No, that isn't necessary, as long as the query returns the fields that you want to add to the table.

[bmoyer123]

So I would just need a button to run module?

[HansV]

Yes, the On Click event procedure of the button could run the WriteDivisions code that I posted.

[bmoyer123]

It worked. Thank you

[HansV]

Great! Thanks for the feedback.