Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

MoBetter

Technical User
Mar 19, 2007
33
US
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...?
 


Hi,

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


Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I'm sorry, but I'm not quite sure what you mean?
 
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...
 



Just

Award

in the click event.

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I did as you suggested; however, a message appears that Microsoft Office Access can't find the macro...
 


Right click your button in your form and select build event

Select code builder

Select the CLICK event.
Code:
Private Sub Command0_Click()
    Award
End Sub

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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?
 


(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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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.
 


replace with this...
Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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
 
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)
 

I noticed this ERROR twice
Code:
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...
Code:
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
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?
 
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]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top