Passing of Argument difficulty 1

[mtnclark]

I have multiple forms and I wish to write to multiple tables from within each form.

The code I use is currently:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Assets&quot

rst.AddNew
rst!EmployeeID = Forms("frmLoanForm&quot.txtEmpID.SetFocus
rst!EmployeeID = Forms("frmLoanForm&quot.txtEmpID.Text
rst!LoanStartDate = Forms("frmLoanForm&quot.txtStartDate.SetFocus
rst!LoanStartDate = Forms("frmLoanForm&quot.txtStartDate.Text
rst!LoanEndDate = Forms
etc etc.
Writing lenghty statements like this over and over is excessive and what I am trying to do is pass the parameters
from my form "frmMain" to "Module1".

The function I have within Module1 takes the stucture of

Function dbWrite(tbl as tableDef, frm as Form)

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tbl&quot

rst.AddNew
rst!EmployeeID = Forms("frm&quot.txtEmpID.SetFocus
rst!EmployeeID = Forms("frm&quot.txtEmpID.Text
rst!LoanStartDate = Forms("frm&quot.txtStartDate.SetFocus
rst!LoanStartDate = Forms("frm&quot.txtStartDate.Text
rst!LoanEndDate = Forms
etc..
End Function

The calling function from one of my forms lets say "frmMain"

Private sub Test cmd_Click()

call module1.dbWrite("Assets",frmMain&quot

End Sub

// This does not work and gives me syntax errors.I think it is the layout or structure of my code.. Any advice would be appreciated. "Assets" is the table I am trying to pass.
--------
Marty..

 

[jebry]

Hi!

The minimum change will need to be like this:

Function dbWrite(tbl as String, frm as String)

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM " & tbl)

rst.AddNew
rst!EmployeeID = Forms(frm).txtEmpID.Value
rst!LoanStartDate = Forms(frm).txtStartDate.Value
rst!LoanEndDate = Forms
etc..
End Function

This will of course work only if each form and table have the same structure. If it is appropriate I can show you how to set this up no matter what the structure of the form and table are as long as you follow some basic naming conventions.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[GHolden]

Try

instead of

Set rst = db.OpenRecordset("SELECT * FROM tbl&quot

This

Set rst = db.OpenRecordset("SELECT * FROM " & tbl.Name)

and for the form

rst!EmployeeID = Forms("frm&quot.txtEmpID.Text

becomes

rst!EmployeeID = frm.txtEmpID.Text

 

[Rolliee]

Instead of 'tbl.Name)' use tbl.Name & ""

Try looking at your SQL by writing it to a variable and then do a MsgBox SQL to see if it is really like you think it is.

rollie@bwsys.net

 

[JohnYingling]

Is that &quot a typo at the end of the statement?
call module1.dbWrite("Assets",frmMain&quot
try taking the " out of there
call module1.dbWrite("Assets",frmMain)

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[mtnclark]

Hi
Thanks to everyone who responded,
I have used the following for my function structure and body.

Function dbWrite(tbl As String, frm As String)

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM " & tbl)

rst.AddNew
rst!EmployeeID = Forms(frm).EmployeeID.SetFocus
rst!EmployeeID = Forms(frm).EmployeeID.Text
rst!Forms(frm).txtStartDate.SetFocus
rst!LoanStartDate = Forms(frm).txtStartDate.Text

This is working,I had thought that table and form would have had to be passed through as their appropriate objects rather than string argument.
Jeff, I would like to take you up on your offer of a uniform setup no matter what the structure of the form and table are as long as you follow some basic naming conventions.
Presumably this just means having the same field names on each form to which I am writing from.

Thanks again..
------------
Marty.

 

[jebry]

Hi Marty!

Sorry it took so long to get back to you but I didn't have a chance to check this site out this weekend. Anyway, you can handle this generically if you will use the following naming conventions, precede you field names in the table with fld and in the form with txt, chk, cbo etc depending on the type of control used to display the data. With this in place you can use the following module:

Function dbWrite(tbl As String, frm As String)

Dim db As Database
Dim rst As Recordset
Dim cntl As Control
Dim strFormName As String
Dim strTableName As String

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From " & tbl)

rst.AddNew
For Each cntl in Forms(frm).Controls
If cntl.ControlType = acTextBox Or cntl.ControlType = acCheckBox etc Then
strFormName = cntl.Name
strTableName = "fld" & Mid(strFormName, 4)
rst.Fields(strTableName) = cntl.Value
End If
Next cntl
rst.Update

Set rst = Nothing
Set db = Nothing

End Function

Now you just pass the name of the form and the name of the table and the function will take care of the rest.

hth
Jeff Bridgham
bridgham@purdue.edu