3 forms, one bit of code, use a module?

S

stuartam

Registered User.
Local time
Today, 08:46
Joined
Jul 16, 2004
Messages
93
Hi guys,

i have 3 forms that all have one button thats the same, so i thourght instead of having the code for the button on each form's button, i could have a module and call it when the button is pushed.
but i cant get it too work, here is my module's code ( module called SesTrkTblSav ):

Code: 
Option Compare Database
Public Sub SesTrkTblSav_Click()
    ' conenction and recordset object variables
    Dim cn As Connection
    Dim vbasuid As String
    Dim vbpackref As String
    Dim vbasstate As String
    Dim vbasdate As String
    
    vbasuid = asuid
    vbpackref = Text6
    vbasstate = asstate
    vbasdate = Dateinputbox
    
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("UserHistory") = vbasuid
        .Fields("PackRef") = vbpackref
        .Fields("StateHistory") = vbasstate
        .Fields("DateHistory") = vbasdate
        .Update ' update the table
        .Close ' close the recordset connection
    End With
    
Exit_SesTrkTblSav:
    Exit Sub
End Sub

the button code to run the module is as follows:

Code: 
Private Sub Command82_Click()
On Error GoTo Err_Command82_Click


    DoCmd.OpenModule "SesTrkTblSav", "SesTrkTblSav_Click"

Exit_Command82_Click:
    Exit Sub

Err_Command82_Click:
    MsgBox Err.Description
    Resume Exit_Command82_Click
    
End Sub

but instead of it running the code it just opens up the code in VB, what am i doing wrong.

best regards
 
All you need to do, in order to run any sub or function, is to put the NAME OF THE SUB OR FUNCTION, not module (not that you did this), in your code, with arguments if applicable, where necessary.

Of course, you used the Open Module Method, which resulted exactly as it should, but to run the code...

Private Sub Command82_Click()
On Error GoTo Err_Command82_Click


Call SesTrkTblSav_Click

Exit_Command82_Click:
Exit Sub

Err_Command82_Click:
MsgBox Err.Description
Resume Exit_Command82_Click

End Sub

"Call" isn't necessary, but helps clarify, that it's a user defined Sub or function.

Hope this helps, Good Luck!
 
Create module called anything and paste this into it...

Code: 
Public Function MyFunction()
    Dim cn As Connection
    Dim vbasuid As String
    Dim vbpackref As String
    Dim vbasstate As String
    Dim vbasdate As String
    
    vbasuid = asuid
    vbpackref = Text6
    vbasstate = asstate
    vbasdate = Dateinputbox
    
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("UserHistory") = vbasuid
        .Fields("PackRef") = vbpackref
        .Fields("StateHistory") = vbasstate
        .Fields("DateHistory") = vbasdate
        .Update ' update the table
        .Close ' close the recordset connection
    End With

End Function


Then you call it on the form like this


Code: 
Private Sub Command82_CLick()
MyFunction
End Sub
 
thanks loads guys for the quick reply's.

i will give it a go in the morning :D

best regards
 
i have tryed the code and it gave me a type mismatch error, but after a bit of messing i have got it too add a new record, but it dosent add any information from my form to the record ( its just a blank record ).

here is what i have:

Code: 
Public Function MyFunction()
   
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("UserHistory") = vbasuid
        .Fields("PackRef") = vbpackref
        .Fields("StateHistory") = vbasstate
        .Fields("DateHistory") = vbasdate
        .Update ' update the table
        .Close ' close the recordset connection
    End With

End Function

button's code:

Code: 
Private Sub save_Click()

    Dim cn As Connection
    Dim vbasuid As String
    Dim vbpackref As String
    Dim vbasstate As String
    Dim vbasdate As String
    
    vbasuid = asuid
    vbpackref = Text6
    vbasstate = asstate
    vbasdate = Dateinputbox

MyFunction
End Sub

thanks for the help guys.
 
Just tryed this and it worked, but it dosent look to be the most efficent way, because i would still have to have a seperate module for each form. how can i get it too read off the values from the form the function was run on and not have to specify the form the values are from in the module.

Code: 
Public Function MyFunction()
   
       ' conenction and recordset object variables
    Dim cn As Connection
    Dim vbasuid As String
    Dim vbpackref As String
    Dim vbasstate As String
    Dim vbasdate As String
    
    vbasuid = Forms![assign packs]![asuid]
    vbpackref = Forms![assign packs]![Text6]
    vbasstate = Forms![assign packs]![asstate]
    vbasdate = Forms![assign packs]![Dateinputbox]
   
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("UserHistory") = vbasuid
        .Fields("PackRef") = vbpackref
        .Fields("StateHistory") = vbasstate
        .Fields("DateHistory") = vbasdate
        .Update ' update the table
        .Close ' close the recordset connection
    End With

End Function

best regards
 
Ah, you are attempting to pass arguments, without passing correctly. You can eithjer, pass tghe values as arguments, or declarec them as public variables, or make an explicit reference.
As arguments...

Public Function MyFunction(vbasuid As String,vbpackref As String, vbasstate As String , vbasdate As String)

' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "TBLPackHistory", cn, adOpenStatic, adLockPessimistic ' open it
.AddNew ' prepare to add a new record
.Fields("UserHistory") = vbasuid
.Fields("PackRef") = vbpackref
.Fields("StateHistory") = vbasstate
.Fields("DateHistory") = vbasdate
.Update ' update the table
.Close ' close the recordset connection
End With

End Function

Then the function call...

Private Sub save_Click()
MyFunction (asuid, Text6, asstate, Dateinputbox)
End Sub



As implicit/explicit reference...

...
.AddNew ' prepare to add a new record
.Fields("UserHistory") = Forms!frmName.asuid
.Fields("PackRef") = Forms!frmName.Text6
.Fields("StateHistory") = Forms!frmName.asstaste
.Fields("DateHistory") = Forms!frmName.Dateinputbox


Hope this offers some insight.
Will stay posted, Good Luck!
 
problem solved, just changed the button code to the following:

Code: 
Public Sub save_Click()
Call MyFunction(asuid, Text6, asstate, Dateinputbox)
End Sub

thanks for all the help m8 its greatly appreciated.
 
That all wroks great, i can now add new records.

but how do i edit existing records, i have the form that loads an existing record and want to have a module that lets me update the CurPackState field off the current record to what ever someone enters into the asstate text box.

now from what little i have read ADO dosent have an "edit" command, but also how do i tell the module what record to edit.

thanks in advance.
 
I tryed this to update the CurPackState field of the current record ( the one opened in the form im running the function from ) but it dosent do anything.

Code: 
Public Function SaveState(vbasstate As String)

' open a connection to the connection object
Set cn = CurrentProject.Connection
' initialise the recordset object
Set rs = New ADODB.Recordset
' using the recordset object
With rs
.Open "TBLPacks", cn, adOpenStatic, adLockPessimistic ' open it
.Fields("CurPackState") = vbasstate
.Update ' update the table
.Close ' close the recordset connection
End With

End Function

button code:

Code: 
Public Sub save_Click()
Call SaveState(asstate)
End Sub

thanks for your help.
 
Stuartam,
Your method, should've updated the FIRST RECORD? It surprises me, that "It doesn't do anything" At All?

You have to at least, be on the record you want to update..

....
With rs
.Open "SELECT * FROM TBLPacks WHERE pkPacksID = " & Forms!frmPacks.pkPackID , cn, adOpenStatic, adLockPessimistic ' open it
.Fields("CurPackState") = vbasstate
.Update ' update the table
.Close ' close the recordset connection
Set rs = Nothing
End With

End Function

Maybe you wanted to edit the 1st record, & still it didn't update?

Maybe try, rs.MoveFirst, before ...(.Fields("CurPackState"....)?

again, I'm surprised something didn't update?
 
...of course, you can pass the ID, as an argument also....

Public Function SaveState(vbasstate As String, iPackID As Integer)

....
.Open "SELECT * FROM TBLPacks WHERE pkPacksID = " & iPackID , cn, adOpenStatic
...

Public Sub save_Click()
Call SaveState(asstate, Me.pkPackID)
End Sub

...unless it is always, the 1st record to be edited (I'd be surprised).

Good luck, either way!
 
thanks so much for all the help.

i have tryed both bits of code and each time i get a 'type mismatch' error, maybe ive spelt something wrong.

the record im testing it on is record 2 ( tryed all the others aswell) in the DB.

thanks for the help
 
which line of code is it blowing up on?
 
its this line:

Code: 
.Open "SELECT * FROM TBLPacks WHERE PackRef = " & iPackID, cn, adOpenStatic
 
Stuartam, a datatype mismatch, is usually trying to pass an argument, of different type, than variable.
Is PackRef an integer or string?
If have it syntaxed, assuming it's an integer.

But, If it's a string...

.Open "SELECT * FROM TBLPacks WHERE PackRef = " & iPackID & "'", cn, adOpenStatic

...either way, Pack Ref must coincide with the Parameter.

Public Function SaveState(vbasstate As String, iPackID As ??????)

Good Luck!
 
DB7 said:
Stuartam, a datatype mismatch, is usually trying to pass an argument, of different type, than variable.
Is PackRef an integer or string?
If have it syntaxed, assuming it's an integer.

But, If it's a string...

.Open "SELECT * FROM TBLPacks WHERE PackRef = " & iPackID & "'", cn, adOpenStatic

...either way, Pack Ref must coincide with the Parameter.

Public Function SaveState(vbasstate As String, iPackID As ??????)

Good Luck!
Click to expand...

Ahh yes - Good eye


If PackRef is defined as a string in the table the SQL must hard code in the quotes.

Didn't know you could do this.

Code: 
.Open "SELECT * FROM TBLPacks WHERE PackRef = " & iPackID [COLOR=DarkRed]& "'", [/COLOR] cn, adOpenStatic

I have been doing this

Code: 
.Open "SELECT * FROM TBLPacks WHERE PackRef = " [COLOR=DarkOrange]chr(34) &[/COLOR] iPackID [COLOR=DarkOrange]& chr(34)[/COLOR],cn, adOpenStatic
 
Yes Surjer, I use that quite often. Especially, when the string may have "quotes" or "apostrophes" itself. This syntax covers that...

WHERE txtStoreName = """ & cboStore & """"

double, double quotes. otherwise, if store name = "Johnson's", the syntax, will get confused.

This won't work...
WHERE txtStoreName = '" & cboStore & "'",
But this will...
WHERE txtStoreName = """ & cboStore & """"
...2 doubles, for every single.

But by the way, you made me spot a small oversight, on my part...

.Open "SELECT * FROM TBLPacks WHERE PackRef = '" & iPackID & "'", cn, adOpenStatic

Thx for that!
 
You must log in or register to reply here.

Similar threads

A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
627
Gasman
Gasman
MajP
Using A Collection Class to Raise Common Event
2
Replies
33
Views
993
Pat Hartman
P
N
Solved I want to be able to send a few emails in access 2016 using gmail but am having challenges
Replies
7
Views
603
nector
N
Uncle Gizmo
How to Simplify MS Access Form Searches Using Class Modules and Collections to Pair Text Boxes and Option Groups for Efficient SQL Query Generation
2
Replies
25
Views
1,463
MarkK
MarkK
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
831
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom