Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-12-2018, 05:07 PM   #1
jsbotts73
Newly Registered User
 
Join Date: Aug 2016
Location: Hobbs, New Mexico, USA
Posts: 18
Thanks: 16
Thanked 1 Time in 1 Post
jsbotts73 is on a distinguished road
Table and Form as Sub Parameters

I am an MS Access rookie. I am trying to create a sub routine in a module and would like to have two parameters in the Sub. The first parameter is the name of a table, and the second the name of a form. In the code below I have only put the type of parameter I would like to put in the sub realizing that it is not correct. I have tried using a string for the table name, and as forms do not have a name property I don't know how to identify a form as a parameter. The code follows:

Public Sub Expenses(tbx As String, frm As Form_frmExpenses) 'Do not know how to insert these parameters
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim strSQL As String
Dim recEID As Integer
"FROM " & tbEx & _
"WHERE " & tbEx & ".[ExpenseID} > 0 " & _
"ORDER BY " & tbEx & ".[ExpenseID];"

Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst
rst.MoveLast
recEID = rst!ExpenseID
frm.tbExpenseID.Value = recEID
recDoP = rst!DateOfPurchase
frm.tbDateOfPurchase.Value = recDoP
rst.Close
Set rst = Nothing
End Sub

jsbotts73 is offline   Reply With Quote
Old 12-12-2018, 05:21 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Table and Form as Sub Parameters

That doesn't look complete yet, but you can have:

Public Sub Expenses(tbx As String, frm As Form)

and call it as

Expenses "TableName", Me

or make both strings:

Public Sub Expenses(tbx As String, frm As String)

and call it:

Expenses "TableName", Me.Name
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
jsbotts73 (12-12-2018)
Old 12-12-2018, 05:29 PM   #3
jsbotts73
Newly Registered User
 
Join Date: Aug 2016
Location: Hobbs, New Mexico, USA
Posts: 18
Thanks: 16
Thanked 1 Time in 1 Post
jsbotts73 is on a distinguished road
Re: Table and Form as Sub Parameters

Thanks for the quick response. I should have made it clear that I have written the sub in a module and the sub to be used in several forms. Because it would not accept Me when used in a form I have to substitute the name of the table and the name of the form.

jsbotts73 is offline   Reply With Quote
Old 12-12-2018, 05:43 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Table and Form as Sub Parameters

Yes, I must not have stated that well. My only use of Me was in calling the sub (from a form), not within the sub.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-12-2018, 05:52 PM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Table and Form as Sub Parameters

Knocking off for dinner soon, so I'll add that using the first method your use of

frm.tbExpenseID.Value = recEID

should work, presuming that control name exists on each form. If you use the second method of pass the name as a string, it would be more like

Forms(frm).tbExpenseID.Value = recEID
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
jsbotts73 (12-12-2018)
Old 12-12-2018, 06:05 PM   #6
jsbotts73
Newly Registered User
 
Join Date: Aug 2016
Location: Hobbs, New Mexico, USA
Posts: 18
Thanks: 16
Thanked 1 Time in 1 Post
jsbotts73 is on a distinguished road
Re: Table and Form as Sub Parameters

Thanks so much pbaldy. I have to knock off for dinner too. After I will try your new suggestions. I appreciate you so much.
jsbotts73 is offline   Reply With Quote
Old 12-12-2018, 08:45 PM   #7
jsbotts73
Newly Registered User
 
Join Date: Aug 2016
Location: Hobbs, New Mexico, USA
Posts: 18
Thanks: 16
Thanked 1 Time in 1 Post
jsbotts73 is on a distinguished road
Wink Re: Table and Form as Sub Parameters

pbaldy: WOW. It now works which will prevent me from having to copy and paste a bunch of code. I used - Sub Expenses(tbx As String, frm As String), and when calling the sub - Expenses "tblExpenses", Me.Name. Now works as intended. Again thank you so much. I had been searching for days on the internet for a solution.

Thanks again. Problem solved!!!

jsbotts73 is offline   Reply With Quote
Old 12-12-2018, 08:52 PM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,247
Thanks: 13
Thanked 4,102 Times in 4,034 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Table and Form as Sub Parameters

Excellent, happy to help!

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
jsbotts73 (12-12-2018)
Reply

Tags
parameters for sub , table name as parameter , using form as parameter , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] form-based parameters, paste output to Excel - "too few parameters" Lifeseeker Modules & VBA 11 12-30-2014 05:05 PM
Put querydef with parameters result into table bobmac- Modules & VBA 3 01-28-2011 01:49 PM
Form's unique table property and stored procedure parameters Dmitriy Forms 10 09-23-2010 07:29 AM
Parameters Table in Access Bullman2000_uk General 3 02-23-2009 01:07 AM
sub-report using same parameters but different table rosensan Crystal Reports 1 11-06-2008 01:24 PM




All times are GMT -8. The time now is 01:18 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World