Automating Table Save

  • Thread starter OldManRiver via AccessMonster.com
  • Start date
O

OldManRiver via AccessMonster.com

All,

Have a form with over 189 controls on it and trying to automate the save.

I currently use the following script to init the fields:
Code: 
On Error GoTo Err_Msg
For Each ctl In Targetform.Controls
' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
Select Case ctl.ControlType
Case 106
If ctl.Visible = True Then ctl = Null
Case 111
If ctl.Visible = True Then ctl = Null
Case 109
If ctl.Visible = True Then ctl = ""
End Select
Next
Exit Sub
Err_Msg:
MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.
Description
So wanting to write something based on temp table containing 2 column
Frm_Fld_Name, and Tbl_Fld_Name for assignment of form field to table field;
maybe looking like this:
Code: 
Dim dbs As DAO.Database, WsP As DAO.Workspace, RsS As DAO.Recordset
SrchNo = Targetform![cboxPSH]
RECnum = DLookup("cad_rno", "tblCADdetail", "[cad_pjx]=" & SrchNo)
WhrStr = "WHERE (([cad_pjx]=" & SrchNo & ") AND ([cad_rno]='" & RECnum &
"'))"
CSTstr = "SELECT * FROM tblCADdetail " & WhrStr & " ORDER BY cad_cds;"
Set Wspace = DBEngine.Workspaces(0)
Set dbs = CurrentDb
Set RsS = dbs.OpenRecordset(CSTstr, dbReadOnly)
With RsS
If .RecordCount > 0 Then
.MoveFirst
.Edit
Else
.AddNew
End If
On Error GoTo Err_Msg
For Each ctl In Targetform.Controls
' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
Select Case ctl.ControlType
Case 106, 109, 111
TF_Name = DLookup("Tbl_Fld_Name","TF_Var_Def","[Frm_Fld_Name]
=" & ctl.Name)
Eval (![TF_Name] = ctl)
End Select
Next
.Update
.Close
End With
Exit Sub
Err_Msg:
MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.
Description
So I do not have to run documenter and cut/paste all vars into the
save subroutine.

Is my idea solid and what are the pit falls?

OMR
 
O

OldManRiver via AccessMonster.com

OldManRiver said:
All,

Have a form with over 189 controls on it and trying to automate the save.

I currently use the following script to init the fields:
Code: 
On Error GoTo Err_Msg
For Each ctl In Targetform.Controls
' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
Select Case ctl.ControlType
Case 106
If ctl.Visible = True Then ctl = Null
Case 111
If ctl.Visible = True Then ctl = Null
Case 109
If ctl.Visible = True Then ctl = ""
End Select
Next
Exit Sub
Err_Msg:
MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.
Description
So wanting to write something based on temp table containing 2 column
Frm_Fld_Name, and Tbl_Fld_Name for assignment of form field to table field;
maybe looking like this:
Code: 
Dim dbs As DAO.Database, WsP As DAO.Workspace, RsS As DAO.Recordset
SrchNo = Targetform![cboxPSH]
RECnum = DLookup("cad_rno", "tblCADdetail", "[cad_pjx]=" & SrchNo)
WhrStr = "WHERE (([cad_pjx]=" & SrchNo & ") AND ([cad_rno]='" & RECnum &
"'))"
CSTstr = "SELECT * FROM tblCADdetail " & WhrStr & " ORDER BY cad_cds;"
Set Wspace = DBEngine.Workspaces(0)
Set dbs = CurrentDb
Set RsS = dbs.OpenRecordset(CSTstr, dbReadOnly)
With RsS
If .RecordCount > 0 Then
.MoveFirst
.Edit
Else
.AddNew
End If
On Error GoTo Err_Msg
For Each ctl In Targetform.Controls
' 100 = Label, 106 = CheckBox, 109 = TextBox, 111 = ComboBox
Select Case ctl.ControlType
Case 106, 109, 111
TF_Name = DLookup("Tbl_Fld_Name","TF_Var_Def","[Frm_Fld_Name]
=" & ctl.Name)
Eval (![TF_Name] = ctl)
End Select
Next
.Update
.Close
End With
Exit Sub
Err_Msg:
MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.
Description
So I do not have to run documenter and cut/paste all vars into the
save subroutine.

Is my idea solid and what are the pit falls?

OMR
Click to expand...

All,

Got to thinking about this for total code re-usability and decided to create
the table as:
tblF2Tmatch:
tfl_id autoincrement
tfl_frm text (Form Name)
tfl_ffd text (Form Field Name)
tfl_tbl text (Table Name)
tfl_tfd text (Table Field Name)
tfl_spc y/n (Special Processing)
tfl_fnc text (SP Function Name)
tfl_prm text (SPF Parms)
Click to expand...
where I can map any field on any form to any
field in any table and then create var assignments for fields needing special
processing and/or something other than direct var to var connection to table
fields.

Thinking on function call something like var = func_nam(form_fld,parmlist)

so var get assigned directly in my process to the table field, like I was
orgininally thinking

Oh, those of you who have not done complete multi-user mode and are stuck in
the "relationship" mode, sorry VBA like this solves those problems.

OMR
 
D

dymondjack

Have a form with over 189 controls on it and trying to automate the save.

Have you tested the performance of this? Just out of curiousity...

190+ controls is a massive amount for Access to handle, and trying run
multiple code loops it's going to a long time to save the form.

--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill
 
O

OldManRiver via AccessMonster.com

dymondjack said:
Have a form with over 189 controls on it and trying to automate the save.
Click to expand...

Have you tested the performance of this? Just out of curiousity...

190+ controls is a massive amount for Access to handle, and trying run
multiple code loops it's going to a long time to save the form.
All,
Click to expand...
[quoted text clipped - 62 lines]
OMR
Click to expand...
Click to expand...


Runs in under 3 seconds on my machine, usually 1.5 or there about.

OMR
 
O

OldManRiver via AccessMonster.com

OldManRiver said:
[quoted text clipped - 8 lines]
Runs in under 3 seconds on my machine, usually 1.5 or there about.

OMR
Click to expand...

All,

My main issue is with the evaluation or interpretation of the names from the
table I created. If I am remembering correctly, VBA has four different way to
interpret and execute a command string and it varies depending on which var
type you have.

What I usually struggle with is getting the second of the following 2 lines
right:
Code: 
          TF_Name = DLookup("tfl_nam", "tblTFmatch", "[tfl_fnm]=" & ctl.
Name)
Eval (![TF_Name] = ctl)
On the note about DLookup, I could
open this table in a recordset, write it to an array and then process out of
an array, if speed is an issue.

OMR
 
O

OldManRiver via AccessMonster.com

OldManRiver said:
[quoted text clipped - 5 lines]
OMR
Click to expand...
Click to expand...
All,

Think I have my code right:
Code: 
Sub Sav_Rec()
Dim dbs As DAO.Database, WsP As DAO.Workspace, RsS As DAO.Recordset, Ffld
As Field
Dim Fnc_Nam, Fnc_Pms, RECnum, SrchNo, SQLstr, TF_Name, WhrStr, Tfld As
Field
SrchNo = Targetform![cboxPSH]
Set Wspace = DBEngine.Workspaces(0)
Set dbs = CurrentDb
DoCmd.Hourglass True
On Error GoTo Err_Msg
For Each Ffld In Targetform.Fields
TF_Name = DLookup("tfl_nam", "tblTFmatch", "[tfl_fnm]=" & Ffld.Name)
If (Ffld.Name <> Ffld.Value) Or Not (IsNull(Ffld.Name) = IsNull(Ffld.
Value)) Then
RECnum = DLookup("cad_rno", "tblCADdetail", "[cad_pjx]=" & SrchNo)

WhrStr = "WHERE (([cad_pjx]=" & SrchNo & ") AND ([cad_rno]='" &
RECnum & "'))"
SQLstr = "SELECT * FROM tblCADdetail " & WhrStr & " ORDER BY
cad_cds;"
Set RsS = dbs.OpenRecordset(CSTstr, dbOpenDynaset)
With RsS
If .RecordCount > 0 Then
.MoveFirst
.Edit
Else
.AddNew
End If
On Error Resume Next
TF_Name = DLookup("tfl_tfd", "tblTFmatch", "[tfl_ffd]=" &
Ffld.Name)
Fnc_Nam = DLookup("tfl_fnc", "tblTFmatch", "[tfl_ffd]=" &
Ffld.Name)
Fnc_Pms = DLookup("tfl_prm", "tblTFmatch", "[tfl_ffd]=" &
Ffld.Name)
On Error GoTo Err_Msg
For Each Tfld In .Fields
If TF_Name = Tfld.Name Then
If IsNull(Fnc_Nam) Or Fnc_Nam = "" Then
Tfld = Ffld.Value
Else
Tfld = Fnc_Nam(Ffld.Name, Ffld.Value, Fnc_Pms)
End If
End If
Next
.Update
.Close
End With
End If
Next
DoEvents
DoCmd.Hourglass False
Exit Sub
Err_Msg:
MsgBox ctl.Name & " => " & "Error => " & Err.Number & " => " & Err.
Description
Resume Next
End Sub
Now testing

OMR
 
O

OldManRiver via AccessMonster.com

Typo

Before
Set RsS = dbs.OpenRecordset(CSTstr, dbOpenDynaset)

After
Set RsS = dbs.OpenRecordset(SQLstr, dbOpenDynaset)
 
O

OldManRiver via AccessMonster.com

All,

Still stuck trying to get the eval/interpret on the call of a function name
to work.

OMR
 
O

OldManRiver via AccessMonster.com

Actually runs quite fast.

Thanks
Have a form with over 189 controls on it and trying to automate the save.
Click to expand...

Have you tested the performance of this? Just out of curiousity...

190+ controls is a massive amount for Access to handle, and trying run
multiple code loops it's going to a long time to save the form.
All,
Click to expand...
[quoted text clipped - 62 lines]
OMR
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with code to "blank" reports 2
Function not running inside a Sub when a date field is blank 2
Loading forms as classes from Listbox click events 0
Loading forms as classes from Listbox click events 0
Validate Data 4
Loading forms as classes from Listbox click events 0
Print Search Listbox Results in Report Filter Preview ... HELP!!! 0

Top