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.

 

[lewds]

Hi...
Global Variants and Global Sub in a separate Module

 

[GulfImages]

Thanks lewds, what I can't figure out is exactly how to code to pull all the fields from the form to mathc up with the table fields, I'm sure a loop but I can't come up with a way. I know the variables would be the form name, table name etc... but I can't get my head around the best way to go about it.

I'm not a programmer by any means but do work with access for several of my accounting clients, probably deeper than many but not to the level of a full fledged programmer.

Thanks.

 

[PHV]

Have a look at the Fields collection of the DAO.Recordset object and at the Controls collection of the Form object.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GulfImages]

Thanks, I'll do that.

 

[TheAceMan1]

How are ya GulfImages . . .

In the [blue]Tag[/blue] property of the controls of interest, add a question mark [blue]?[/blue] (no quotations please). Then try the following:

[blue]   Dim db As DAO.Database, rst As Recordset, ctl As Control
   
   Set db = DBEngine.Workspaces(0).Databases(0)
   Set rst = db.OpenRecordset("tblVendors", DB_OPEN_TABLE)
   
   rst.AddNew
      For Each ctl In Me.Controls
         If ctl.Tag = "?" Then rst(ctl.Name) = ctl
      Next
   rst.Update
   
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[GulfImages]

That looks perfect, I'll try that. If every control on the form applies, then I assume this would work?:

For Each ctl In Me.Controls
Then rst(ctl.Name) = ctl
Next
rst.Update

I like the ? idea because some of the forms may have controls that don't get written.

Thanks again.

 

[GulfImages]

Just one other quick thing if you don't mind. I would like to make "tblVendors" a variable, sometimes these things are tricky about how to insert a variable. Would the syntax just be something like?:

Dim mytable 'the name of the table to use

Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("mytable", DB_OPEN_TABLE)

I know I can work out the syntax but if it's something different I'd appreciate the push in the right direction.

Thanks again, I'm very grateful.

 

[PHV]

mytable = "tblVendors"
Set rst = db.OpenRecordset(mytable, DB_OPEN_TABLE)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GulfImages]

mytable would actually be declared in the sub so I could change it for each form ie...

Sub AddNew(mytable, etc, etc)

 

[TheAceMan1]

GulfImages . . .

BTW you can replace:
[blue]Set db = DBEngine.Workspaces(0).Databases(0)[/blue]
with
[blue]Set db = CurrentDb[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[GulfImages]

That was fast!, I tried to follow up quickly, but I think you answered my question, thanks.

I would need to use the quotes when using the sub?:

AddNew("tblSuppliers", etc, etc)

 

[GulfImages]

Thanks AceMan1, I know I'm still using old techniques.

 

[TheAceMan1]

Woops! . . .

rst As Recordset
should be
rst As DAO.Recordset

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[GulfImages]

I'm almost there:

Here is what I ended up with:

Sub AddNewRecord(mytable, myform As Form)

Dim db As DAO.Database, rst As DAO.Recordset, ctl As Control
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset(mytable, DB_OPEN_TABLE)
   
   rst.AddNew
      For Each ctl In myform.Controls
         If ctl.Tag = "D" Then rst(ctl.Name) = ctl
      Next
   rst.Update
   rst.Close
   
    Set rst = Nothing
    Set db = Nothing

End Sub

When I try to use it in my code like this:

AddNewRecord("tblCustomers", "frmCustomers")

I get a complie error, expected =

I'm sure this is just syntax, my weak point.

Thanks again guys, this is going to really help me out.

Bobby

 

[GulfImages]

I actually already have a variable declared for the form within the code already running it's named CF I don't know if I could use that variable inside my AddNewRecord("tblCustomers", "CF") sub or something like that.

 

[TheAceMan1]

GulfImages . . .

In the header for the routine [blue]myform As Form[/blue] is a form object not a string! From any form the call should be:

[blue]AddNewRecord("tblCustomers", [purple][b]Me[/b][/purple])[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[GulfImages]

Okay, the reason I was worried about using Me was there may be 2 different forms open at a given time that both use this sub to update records (couldn't ever be at the exact same time), could there be a conflict? If I wanted to explictly declare the form by name wouldn't it be?

AddNewRecord("tblCustomers", frmCustomers)

Or using the already set variable of CF?:

AddNewRecord("tblCustomers", CF)

The code works perfect when I take the form variable out and add it directly to the code:

For Each ctl In Forms!frmCustomers.Controls

All I need to do is be able to variable the form and this is a done deal.

Thanks.

 

[GulfImages]

Actually, when I try to type in using Me,

AddNewRecord("tblCustomers", Me)

I get the expected = error when I press enter as well so I can't test it.

 

[PHV]

Use either:
Call AddNewRecord("tblCustomers", Me)
Or:
AddNewRecord "tblCustomers", Me

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886