Making this work with any form 3

[GulfImages]

I have a database that uses all unbound forms as it was the only way I could take total control of everything, it works fine but I have a few sub routines that I think could be better made to work with any form. As it is now, I have to code all this for each form. Any suggestions as to how I could get this into a routine that would work with any form?

All field names on the form will always be the same as the table fields and I'd like to make a sub that cycles through the form gathering all the field names to update to the table, obviously there are several items that would have to be variable.

This is a sub for adding a new record from an unbound form.

This may be a lot to ask and any ideas would be very helpful. I know I may be doing some things the old way.

Thanks.


Sub AddVen()

StopVen = 0

'Stop Ven and editingVen are global variables that have already been set.

If editingVen = -1 Then SaveVen
Exit Sub
End If

Do some validation stuff here, removed most to keep short

Dim VF As Form
Set VF = Forms!frmSuppliers

If (DCount("[SupplierName]", "tblVendors", "[SupplierName] = Forms!frmSuppliers![SupplierName]")) > 0 Then
MsgBox "The Supplier Name entered has already been used.", 48, "Invalid Supplier Name"
VF!SupplierName.SetFocus
StopVen = -1
DoCmd.CancelEvent
Exit Sub
End If

'This is the beginning of the part I'd like to generalize
'to work with any form, all form fields and table fields
'will have the same name

DoCmd.Echo 0
DoCmd.Hourglass -1

Dim AddVens As Database, AddVentbl As Recordset
Set AddVens = DBEngine.Workspaces(0).Databases(0)
Set AddVentbl = AddVens.OpenRecordset("tblVendors", DB_OPEN_TABLE)

AddVentbl.AddNew

AddVentbl!SupplierName = VF!SupplierName
AddVentbl!ContactName = VF!ContactName
AddVentbl!ContactTitle = VF!ContactTitle
AddVentbl!Address1 = VF!Address1
AddVentbl!Address2 = VF!Address2
AddVentbl!City = VF!City
AddVentbl!State = VF!State
AddVentbl!Zip = VF!Zip
AddVentbl!PhoneNumber = VF!PhoneNumber
AddVentbl!FaxNumber = VF!FaxNumber
AddVentbl!Website = VF!Website
AddVentbl!VenTypeID = VF!VenTypeID
AddVentbl!Notes = VF!Notes

AddVentbl.Update
AddVentbl.Close

ClearVen 'Sub to clear form


DoCmd.Echo -1
DoCmd.Hourglass 0

End Sub

Thanks again for looking.

 

[GulfImages]

All I needed was to use Call:

Call AddNewRecord("tblCustomers", Forms!frmCustomers)

I also found my already declared and set form variable can be used:

Call AddNewRecord("tblCustomers", CF)

This great! I really appreciate it. I've looked around here to try and contribute and if I can I will try in return, but I'm way behind many of you guys.

Thanks Again!