Applying Modules to Queries, in Access, so it runs automatically

[MoBetter]

I was wondering once a Module is created, how does the actual Module work in a Query, so that the Code would run, behind the scenes, automatically when I apply an Update/Refresh Button to a Form.

Please note the original Code written by Remou:

Sub Award()
Dim rs As DAO.Recordset
Dim rsA As DAO.Recordset

strSQL = "SELECT t.CustomerID, Rnd([CustomerID]) AS RandCust, t.AwardID, t.AwardDate " _
& "FROM CustomerAwards t " _
& "WHERE t.AwardID Is Null " _
& "ORDER BY Rnd([CustomerID])"
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT AwardID FROM Awards"
Set rsA = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()

rs.Edit
rs!AwardID = rsA!AwardID
rs!AwardDate = Format(Date(),"yyyy/mm/dd")
rs.Update

rs.MoveNext
rsA.MovenextLoopEnd Sub

-------
Now I just need the Code to run behind the scenes when a botton is pressed on a Form to activiate the process of the Module whereas that same data/information will be sent to a Report.

(1) Do I need to create a separate Query for this to happen, if so, how? OR

(2) Do I just add a button to a Form and place a separate Code on the button, if so, how? OR

(3) Do I create a Macro to activate the Module, if so, how?

Thus far, I can only execute the Module by pressing Run in the Module window. I'm sure there is a cleaner way to perform/execute this process. Any suggestions...?

 

[SkipVought]

Hi,

you add a button to your form and call Award from the button click event.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

I'm sorry, but I'm not quite sure what you mean?

 

[MoBetter]

I know much; and still learning, so I guess I'm not too savvy...

How do I call Award from the click event. I went to the event and I'm not sure if I'm supposed to write another code...

 

[SkipVought]

Just

Award

in the click event.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

I did as you suggested; however, a message appears that Microsoft Office Access can't find the macro...

 

[SkipVought]

Right click your button in your form and select build event

Select code builder

Select the CLICK event.

Private Sub Command0_Click()
    Award
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

I don't know what I did wrong, but the same error message appears.

(1) I selected the Command button option
(2) I cancelled the Command Button Wizard
(3) I right-clicked on the Command Button and selected the Code Builder
(4) I deleted the initial Code for the Button and Paste the Code you provided.
(5) I Closed and Saved the Form.

When I reopened it, nothing happened. The data remained the same. Was there something else that I was supposed to do?

 

[SkipVought]

(4) I deleted the initial Code for the Button and Paste the Code you provided.

Please paste

1) the code that you deleted and

2) the code that you finally keep.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

You are extremely helpful and patient... I put the initial Code for the button back in there, so the Code looks like this:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub

Private Sub Command0_Click()
Award
End Sub


I am still receiving an error message. The message produced is:

The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: Command0_Click.

 

[SkipVought]

replace with this...

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

    Award

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

This assumes that you have a procedure named Award in a module.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

Below is the code in the module:

Sub Award()
Dim rs As DAO.Recordset
Dim rsA As DAO.Recordset

strSQL = "SELECT t.CustomerID, Rnd([CustomerID]) AS RandCust, t.AwardID, t.AwardDate " _
& "FROM CustomerAwards t " _
& "WHERE t.AwardID Is Null " _
& "ORDER BY Rnd([CustomerID])"
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT AwardID FROM Awards"
Set rsA = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()

rs.Edit
rs!AwardID = rsA!AwardID
rs!AwardDate = Format(Date(),"yyyy/mm/dd")
rs.Update

rs.MoveNext
rsA.MovenextLoopEnd Sub

-------
I saved the module as Tempone

 

[MoBetter]

I did what you suggested and an error message generated:

Compile Error:

Sub or Function not defined.

(The first line of your Code is highlighted in yellow)

Private Sub Command0_Click()

(The word Award is shade in a grey box)

 

[SkipVought]

I noticed this ERROR twice

Sub Award()
Dim rs As DAO.Recordset
Dim rsA As DAO.Recordset

strSQL = "SELECT t.CustomerID, Rnd([CustomerID]) AS RandCust, t.AwardID, t.AwardDate " _ 
      & "FROM CustomerAwards t " _
      & "WHERE t.AwardID Is Null " _
      & "ORDER BY Rnd([CustomerID])"
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT AwardID FROM Awards"
Set rsA = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()

    rs.Edit
    rs!AwardID = rsA!AwardID
    rs!AwardDate = Format(Date(),"yyyy/mm/dd")
    rs.Update

    rs.MoveNext
    [b][red]rsA.MovenextLoopEnd Sub[/red][/b]

it SHOULD be...

Sub Award()
Dim rs As DAO.Recordset
Dim rsA As DAO.Recordset

strSQL = "SELECT t.CustomerID, Rnd([CustomerID]) AS RandCust, t.AwardID, t.AwardDate " _ 
      & "FROM CustomerAwards t " _
      & "WHERE t.AwardID Is Null " _
      & "ORDER BY Rnd([CustomerID])"
Set rs = CurrentDb.OpenRecordset(strSQL)

strSQL = "SELECT AwardID FROM Awards"
Set rsA = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF()

    rs.Edit
    rs!AwardID = rsA!AwardID
    rs!AwardDate = Format(Date(),"yyyy/mm/dd")
    rs.Update

    rs.MoveNext[b]
    rsA.MovenextLoop
End Sub[/b]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

Sorry about that. It was the way I copied it. The Code; however, isn't like that in the db.

Am I doing something incorrectly with the one you supplied?

 

[SkipVought]

When you right-click on the button in the form editor, and select Build event..., a window opens named

Microsoft Visual Basic - your database name - [Form_your form name (Code)]

In this code window are all the Form Objects and Object Events.

Is this what you see?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MoBetter]

Yes