Hi all

Tx in advance to anyone helping :)

I have this VBA code as an event on a form called frmTransaction.

I would like to convert this code into an embedded macro.

I've done it with many simpler VBA code, but this one seems above me

Could someone please write out how a macro would be made using this code (if possible)

Event Procedure:

Private Sub SellerIDAccount_AfterUpdate()


'Look up seller commission and assign it to ComSell control
Dim strFilter As String

strFilter = "CustomerID = Forms![frmTransaction]!SellerID"
Me!ComSell = DLookup("ComSell", "tblCustomer", strFilter)
Me!ChargeSellOn = DLookup("SellComOn", "tblCustomer", strFilter)
Me!EmployeeSeller = DLookup("AccountManager", "tblCustomer", strFilter)
Dim stDocName As String

stDocName = "macfrmTransactionCalculateFees"
DoCmd.RunMacro stDocName



End Sub

Normally people ask to convert macros to vba but you're asking for the opposite. Very rare :)

Unfortunately, there's no button to do this and you would find that most professionals on here don't use macros for the task you're performing.

What's your reason for wanting to convert?

By the way, why don't you use a query in place of the three DLookups? I can see they are all referring to the same table and have the same criteria.

Welcome to the forum by the way.

Normally people ask to convert macros to vba but you're asking for the opposite. Very rare :)

Unfortunately, there's no button to do this and you would find that most professionals on here don't use macros for the task you're performing.

What's your reason for wanting to convert?

By the way, why don't you use a query in place of the three DLookups? I can see they are all referring to the same table and have the same criteria.

Welcome to the forum by the way.

Heyy tyvm for welcome note!!!

It is first time posting!...i have used the forms for so much info for long time, and yes it is rare...i tryed googling this for many months and found many solutions to other things along the way, but alas i have exhausted my search..and got off the lazy couch and decided to post question :)

I did not write the VBA, i believe it was made from a wizard making the combo box in a database i made in earlier versions, i just copied it casue i never could do this code on my own. When i upgraded to 2007 access i read all the hype about macros and i converted most my vba to macros on forms embedded in controls. Why i like it this way, or so it seems...is when i copy the form for other use the macros still all work and no need for me to do anyhting else, where if i had them in VBA code etc i would have to go change parts where it refers to the current form i am using. (ex. I use frmTransaction (a form) and i enter transactions on this form, so the code it seems to all refer to frmTransaction. If i made a copy called frmTransactionCopy, then in the VBA code i would have to change all the parts with the name frmTransaction to frmTransactionCopy etc. Also it seems to be easier for me to move forward with things and macros seem to be easier for me to understand etc. Beyond that..i prob could write a novel on my thoughts, but it just seems more compact, tight and secure to me...my skill level is ok..but..as the saying, "When the student is ready, the teacher will appear"
... I am open to any and all help :)

Thanks again
Dale

Normally people ask to convert macros to vba but you're asking for the opposite. Very rare :)

Unfortunately, there's no button to do this and you would find that most professionals on here don't use macros for the task you're performing.

What's your reason for wanting to convert?

By the way, why don't you use a query in place of the three DLookups? I can see they are all referring to the same table and have the same criteria.

Welcome to the forum by the way.

ohh..as far as query, i am not sure why...i dont know if i know how to make it like the code above...am open to your thoughts of course and again..tyvm for any help in advance :)

With respect to the "Me." reference are you saying that you have other forms that use ComSell, ChargeSellOn, EmployeeSeller fields and you would like to call just one code for all those forms?

With respect to the "Me." reference are you saying that you have other forms that use ComSell, ChargeSellOn, EmployeeSeller fields and you would like to call just one code for all those forms?

yes, i believe that is what i saying, but...as below and my example, if i had another form that i wanted the same procedure done but the form was named frmTransactionCopy, then i would have to change the code that i bolded below
Private Sub SellerIDAccount_AfterUpdate()


'Look up seller commission and assign it to ComSell control
Dim strFilter As String

strFilter = "CustomerID = Forms![frmTransaction]!SellerID"
Me!ComSell = DLookup("ComSell", "tblCustomer", strFilter)
Me!ChargeSellOn = DLookup("SellComOn", "tblCustomer", strFilter)
Me!EmployeeSeller = DLookup("AccountManager", "tblCustomer", strFilter)
Dim stDocName As String

stDocName = "macfrmTransactionCalculateFees"
DoCmd.RunMacro stDocName



End Sub

I will show you a way of having one function for all. Two questions:

1. Do you know how to create a module?
2. What is the datatype of CustomerID?

additonally..what im saying..for me..is i can copy this control if it has embedded macro and drop on any form and it works..without doing any code...or so it seems to me and my experience with other things i converted, but again..i am open to any thoughts etc :)
dale

I will show you a way of having one function for all. Two questions:

1. Do you know how to create a module?
2. What is the datatype of CustomerID?

1)..hmmm..i think so
2) long integer, its the key, with autonumber on it as well

So what you do is put this code in a new module and ensure that the name of the new module is not the same as the name of the function:
Public sub RunTransactMacro(intSellerID as Variant, objForm as Form)
'Look up seller commission and assign it to ComSell control
Dim strFilter As String

strFilter = "CustomerID = " & Nz(intsellerid, 0)
objForm.Form!ComSell = DLookup("ComSell", "tblCustomer", strFilter)
objForm.Form!ChargeSellOn = DLookup("SellComOn", "tblCustomer", strFilter)
objForm.Form!EmployeeSeller = DLookup("AccountManager", "tblCustomer", strFilter)

DoCmd.RunMacro "macfrmTransactionCalculateFees"
end subOr here's the query/recordset option I was talking about:
Public sub RunTransactMacro(intSellerID as Variant, objForm as Form)
'Look up seller commission and assign it to ComSell control
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT ComSell, SellComOn, AccountManager FROM tblCustomer " & _
"WHERE CustomerID = " & Nz(intSellerID, 0))
With rst
If .RecordCount <> 0 Then
.MoveFirst
objForm.Form!ComSell = !ComSell
objForm.Form!ChargeSellOn = !SellComOn
objForm.Form!EmployeeSeller = !AccountManager
Else
objForm.Form!ComSell = Null
objForm.Form!ChargeSellOn = Null
objForm.Form!EmployeeSeller = Null
End With
End With

rst.Close
Set rst = Nothing

DoCmd.RunMacro "macfrmTransactionCalculateFees"
end subTry both.

To call the function you put this:
RunTransactMacro Me!SellerID, MeFor the second one you might need to add the Microsoft DAO 3.x Object Library under TOOLS > REFERENCES in the vba editor.

i will try this..tyvm and will post back here, also, can that be made into a macro?

ok...on the first part..havent attempted query yet

i made module called: RunTransactModuleSeller
it has this code:

Option Compare Database
Option Explicit

Public Sub RunTransactMacro(intSellerID As Variant)
'Look up seller commission and assign it to ComSell control
Dim strFilter As String

strFilter = "CustomerID = " & Nz(intSellerID, 0)
Me!ComSell = DLookup("ComSell", "tblCustomer", strFilter)
Me!ChargeSellOn = DLookup("SellComOn", "tblCustomer", strFilter)
Me!EmployeeSeller = DLookup("AccountManager", "tblCustomer", strFilter)

DoCmd.RunMacro "macfrmTransactionCalculateFees"
End Sub

i have on control (combo box)where i select seller a after update event with the code as follows:

Private Sub SellerIDAccount_AfterUpdate()

RunTransactMacro (Me!SellerID)

End Sub


i believe this is what u said, seems when change seller the code window pops up with an compile error invalid use of me keyword
and it highlites below:

Public Sub RunTransactMacro(intSellerID As Variant)
'Look up seller commission and assign it to ComSell control
Dim strFilter As String

strFilter = "CustomerID = " & Nz(intSellerID, 0)
Me!ComSell = DLookup("ComSell", "tblCustomer", strFilter)
Me!ChargeSellOn = DLookup("SellComOn", "tblCustomer", strFilter)
Me!EmployeeSeller = DLookup("AccountManager", "tblCustomer", strFilter)

DoCmd.RunMacro "macfrmTransactionCalculateFees"
End Sub


part of the reason i think i asked bout macros...is i may /may not chneg things in future and macros seem easier for me to understand...code seems difficult when i have a problem etc...

any thoughts...or did i miss somehting?


and on the query part..that u also wrote out stuff for...where do i put that code?

tyvm for any help in advance:)

dale

Oops... looks like I forgot to amend that part. It was very early in the morning here :)

I've amended the code above so read through the whole thing again.

For the 2nd one, you will put it in a module as well. Try the first one and when it works, you can comment it out and put the second and try it out. See which one runs faster for you.

Oops... looks like I forgot to amend that part. It was very early in the morning here :)

I've amended the code above so read through the whole thing again.

For the 2nd one, you will put it in a module as well. Try the first one and when it works, you can comment it out and put the second and try it out. See which one runs faster for you.



ohh u rock m8!
ill try out and respond
tyvm
dale

Let us know if that works for you.

Let us know if that works for you.

the first one u gave works awesome! tyvm!

i have not yet attempted to try second one, but i will too

i soo appreciate all ur help
dale

No problemo! See how the other one goes.