Call up Module Function

[coolcatkelso]

Hiya

I have this module

Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
>
Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"
>
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close
>
Set rs = Nothing
Set db = Nothing
End Function

How would I refer to it to run it from a button?

I want to use the insert into and run it from a button
________
Bmw Dixi History

 

[EraserveAP]

I would do this:

Dim Some_Variable as String ‘Your data type should be LastID’s data type preferably.

'Use your variable to call the function
Some_Variable = ShowIdentity

I see no reason for that not to work.

Ideally you would want the Function as a specific variable (Whatever LastID’s data type is) and not a variant if you can. I am pretty sure Variant is the slowest data type in VBA.

 

[coolcatkelso]

lol thanks for the reply

Unfortuantly, I have no idea where to start
________
SC2 TERRANER REPLAYS

 

[WayneRyan]

Kelso,

When you declare your function (in a module):

Public Function ShowIdentity() As Variant
...
End Function

Then on your button:

Dim varTest As Variant
varTest = ShowIdentity()

Since it has no arguments, and doesn't return a value, it could be

Public Sub ShowIdentity()

Then, on your button

Call ShowIdentity

The key is to make the Sub or Function PUBLIC.

hth,
Wayne

 

[coolcatkelso]

Hiya Wayne

Thanks for the reply m8

Right, lets see if I got this

I just copy the Function script into a module and save it

On my main form where I want the command to run, I click on button Props, and ONClick VBA - enter this

Dim varTest As Variant
varTest = ShowIdentity()

And rename to the button to ShowIdentity?

Cheers
Harry
________
Wendie 99

 

[EraserveAP]

Thanks for the assist Wayne, I was going to upload a DB using Kelso's function however it is missing a few things. Without knowing more details about what it is supposed to do aside from returning LastID, I assume this is the purpose, I hesitate to modify it and submit it.

 

[EraserveAP]

Hiya Wayne

And rename to the button to ShowIdentity?

Cheers
Harry

You would want to rename the command button to something like cmdShowIdentity. You do not want it to have the same name as your function or Public Sub.

 

[WayneRyan]

I didn't read it close enough. It does return a value.

Your command button can simply have:

Me.SomeTextBox = ShowIdentity()

I was just concentrating on the mechanics of calling it.

Wayne

 

[coolcatkelso]

Hiya EraserveAP

All in all, I have an append query in place to copy a record from Quotes to Workorders. I have the main form working with the append, but not the Subform. From my understanding over the posts, I need to retrieve the WorkorderID that the append created and have it in the subform to show the Quote Subform on the Workorder Subform

So this module should allow me to get the new WorkorderID and I should be able to get it into the Quotes, Subform, WorkorderID field

I think

Heres a copy of the DB with the working Append

Hopefully it will all make sense
________
Meliya

 

[EraserveAP]

I have Access 2007 at work, but here at home I have only 2003 so your example, at least for me will have to wait until I get to work tomorrow. Possibly someone else could take a look.

The one function you posted in this thread however is not going to work just by copy/pasting into a module though it requires some tweaking to match your table and fields. The select query portion looks flawed to me though in it. You may want to use the query builder and create a working query and then select SQL view. You could then add in changes as needed.

 

[coolcatkelso]

lol ok I'm lost

I think I just might do a double entry lol and re-type the Workorder, It sounds easier

As I said, I'm still a basic novice lol
________
Washington Medical Marijuana Dispensaries

 

[EraserveAP]

If no one helps you before I get to work tomorrow I am willing to see what you have and assist then.

 

[coolcatkelso]

ok m8 that woul dbe great

Very much appreciated
________
Bong Reviews

 

[EraserveAP]

I looked at your Database, there is a lot going on.
I think I understand what you are trying to do with the Function but your forms are a bit busy.
I copied a few tables from your sample to mine and created a form and Function.
Simply Click the button to run an append a query and Return the Last WorkOrderID.

So take a look at what I have uploaded and see if this would help you out.

 

[coolcatkelso]

wow that looks so easy. Just need to attempt this on my own DB and see what resuilt I get..

I just the new Workorder to be copied into the Quote Subform, then that subform would link itself to the Workorder, using the new workorderID

So yes, I think you hit it on the head there

Can't thank you enough.. I'm sure I'll be back in touch lol when I attempt it on my own DB tho
________
N02 vaporizer

 

[EraserveAP]

I hope it works for you, you will want to remove the " ' " from the 'On Error GoTo errhandler in your function once you have it how you want it. I forgot to remove it prior to uploading.

 

[coolcatkelso]

ok Will do

Had a look through in design view and noticed that you had the new workorderID going to an unbound field..

The way I would want it, is to copy the new workorderID to the subform.WorkorderID Bound field.. Would this make a difference? or do I need it as unbound
________
SPICE WEED

 

[EraserveAP]

You mean the textbox I set to the WorkOrderID's last value?
It was simply easier for me to set its value while unbound and was personal preference, be that right or wrong.
If you can bind it and get it to display the value you wish, when you wish, then by all means go ahead and do so. That may limit its flexibility a bit, but should still function as you desire.

 

[coolcatkelso]

Hiya m8

Just playing around with the sample you sent, and trying to add the forms as it looks with mine. Customers, etc

I created a button on the Quotes page, called it Execute

I assume I need to have the button run the append query First, then your execute code to get the code back?
________
HOTELS IN MEXICO CITY

 

[coolcatkelso]

ah I see now, I see you have the append query running the Module itself so when the button is click it does it all.
________
UGGS