macro that calls other macro (1 Viewer)

dedjloco · Mar 24, 2017

So I created a code that is working on itself, but when I try to make it "parameterizable" so I can call it multiple times it doesn't.
Here is the code:

Code:

Public Sub CurrentRecordMacro(MyObject As Object, TableField As Object)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblProductSpecs")
   rst.MoveFirst
   Do Until rst.EOF
      If rst("Project Nr") = Me.Project_Text Then
         MyObject = rst!TableField
      End If
      rst.MoveNext
   Loop
End Sub

and the I call it like this:

Code:

CurrentRecordMacro Me.BH_Check, Product_BH

So I should be able to put every object from my form in there and the matching table field.
When I put Me.BH_Check and Product_BH in the code itself it works. So probably something is wrong with the data types of the user defined variables. But I don't know what it should be then?

Ranman256 · Mar 24, 2017

That isn't a macro ,it is a procedure.
You CAN call Macros to repeat. In a real macro, set
Runacro Mac, repeats

You could call your procedure in it.

dedjloco · Mar 24, 2017

I think you mis understood me. I meant that I want to run this more that once but with diverent variables.
So I came up with this so I don't have to put this code in a 1000 times but just call the macro multiple times but for each instance with other variables.

Minty · Mar 24, 2017

Ranman is correct though, these are not macro's they are VBA procedures. Calling them macro's will confuse many viewers / user of this forum. Macro's are another thing in Access.

What is the purpose of this procedure? Can you tell us in plain terms? It looks as if you are trying to set multiple fields values? This would normally be better done with an update query.

jdraw · Mar 24, 2017

Here is a link that describes Access and its objects

dedjloco · Mar 27, 2017

I started an other thread on this topic where I asked the question how I could solve this the best way and I was told that I could better do it with an procedure.
https://access-programmers.co.uk/forums/showthread.php?t=292576
maybe you can tell me if its better with an query or not?
and how I would do that.

Ranman256 · Mar 27, 2017

Code:

Sub RunMacroRepeat(byval iRepeat as integer)
For I = 1 to iRepeat
   CurrentRecordMacro obj, table
Next
End sub

dedjloco · Mar 27, 2017

Can you tell me what tis code does? Because I don't understand how I should integrate this in my program. and how this would solve that I want to assign different variables eachtime I call it.

Minty · Mar 27, 2017

Having read that other thread, you are appear to be trying to populate a unbound form, by looping through controls on your form?.
I wouldn't bother, simply list the controls and set them all at once with the recordset you have opened. Currently you would be opening a recordset for each control which is very wasteful.

dedjloco · Mar 27, 2017

oke that worked pretty good. Now the only thing left from the original thread is when I selected a "Project Nr." to put the values I change in the correct recordset.
I could make a procedure for that as well, but can't it be done differtly? Because It already works partially. Because it puts everything in the first record. But how can I switch between the selected record?

macro that calls other macro (1 Viewer)

dedjloco

Registered User.

Ranman256

Well-known member

dedjloco

Registered User.

Minty

AWF VIP

jdraw

Super Moderator

dedjloco

Registered User.

Ranman256

Well-known member

dedjloco

Registered User.

Minty

AWF VIP

dedjloco

Registered User.

Similar threads

Users who are viewing this thread