unbound fields / unbound forms / sql queries

[Bernie]

I have written some routines to bypass one of access' weaknesses in the sense
that I only link the forms and bind the fields to the underlying table once a
user has entered certain data. The entered data is converted into sql strings
and then I link the search result to the form. By doing so, access becomes
very fast and I have no performance problems in a lan environment. I first
saw this concept back in 1995 and now I am wondering if I have to take the
burden of writing such routines or whether there is an easier way perhaps
because access 2003 offers better standard functionality.

Any comments most welcome.

 

[Marshall Barton]

I have written some routines to bypass one of access' weaknesses in the sense
that I only link the forms and bind the fields to the underlying table once a
user has entered certain data. The entered data is converted into sql strings
and then I link the search result to the form. By doing so, access becomes
very fast and I have no performance problems in a lan environment. I first
saw this concept back in 1995 and now I am wondering if I have to take the
burden of writing such routines or whether there is an easier way perhaps
because access 2003 offers better standard functionality.

I haven't felt the urge to do an unbound data form since
1993 ;-)

I think the "right" way is to use a bound form, but make
sure all the search criteria is used when any data is
needed.

That might mean that the form's record source is set to an
empty string when the form opens and the appropriate select
query is constructed and assigned when the filter
information becomes available.

Alternatively, the record source can be a query that uses
the filter text/combo boxes in its criteria, but the form's
AllowEdits/AllowDeletions properties are set to False so no
records are retrieved. When the filter information becomes
available, then AllowEdits is set to True.

 

[Bernie]

Dear Marshall

Thank you for your explanation. I wrote a routine to create the sql string
like this:


----------------------------------------------------------------------------------------------
Public Function SearchForm(strmainform, strTabSource)

strMid = ""
strKrit = ""
strSQL = ""

Set db = DBEngine(0)(0)
Dim rs As Recordset
Dim tempDate As String

Dim ystrSQL As String
Dim xstrSQL As String
ystrSQL = "select * from "
ystrSQL = ystrSQL & strTabSource & " "
'xstrSQL check SQL string to see if data has been found
xstrSQL = ystrSQL
Dim k As Integer
Dim tempCounter As Integer
tempCounter = 0
Dim counter As Integer
counter = 0
Dim tempStr As String

'select fields with tag 1 or 2, 1 for data and 2 for date format
'check if data is entered, no data ---> counter = 0, data entry yes ---->
counter > 0

tempCounter = 0

For k = 0 To Forms(strmainform).Controls.Count - 1
If Forms(strmainform).Controls(k).Tag = 1 Or
Forms(strmainform).Controls(k).Tag = 2 Or Forms(strmainform).Controls(k).Tag
= 3 Then
If Forms(strmainform).Controls(k) <> "" Then
counter = counter + 1
End If
End If
Next k

If counter > 0 Then
ystrSQL = ystrSQL & "where "
Else

'Dim Msg, Style, Title, Help, Ctxt, Response, MyString
'Msg = "No entries! Please enter data." ' Define message.
'Style = vbInformation ' Define buttons.
'Title = "Search Result" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If

For k = 0 To Forms(strmainform).Controls.Count - 1

If Forms(strmainform).Controls(k).Tag = 1 Or
Forms(strmainform).Controls(k).Tag = 2 Or Forms(strmainform).Controls(k).Tag
= 3 Then

If Forms(strmainform).Controls(k) <> "" Then
tempCounter = tempCounter + 1
strMid = ""

'evaluate numerical, date, text

strDataFlag = 0
'1 = numerical (0...9)
'2 = date (07/25/2007)
'3 = text (A....Z, 0....9)
'4 = ...

If Forms(strmainform).Controls(k).Tag = 1 Then
strDataFlag = 1 'H001, T001...
ElseIf Forms(strmainform).Controls(k).Tag = 2 Then
strDataFlag = 2 'date 07/25/2007
ElseIf Forms(strmainform).Controls(k).Tag = 3 Then
strDataFlag = 3 'text
End If

strKrit = Forms(strmainform).Controls(k)
strLen = Len(strKrit)

'evaluate strMid for SQL query

'option strMid definition
' case -----> strMid
' nothing -----> "="
' "<>" -----> "IS NOT"
' ">" -----> ">"
' "<" -----> "<"
' ">0" -----> "LIKE"
' "*" -----> "LIKE"

strLen = Len(strKrit)

If Left(strKrit, 2) = "<>" Then 'And Len(strKrit) > 2 Then
strMid = "<>"
strLen = Len(strKrit) - 2
strKrit = Right(strKrit, strLen)
MsgBox ("strKrit : " & strKrit)
'check and convert to sql date US American format

If strDataFlag = 2 Then
strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Then
'nothing, don't include them into SQL string
ElseIf strDataFlag = 3 Then
'...to follow

End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & strMid & " " & strKrit


ElseIf Left(strKrit, 1) = "<" And Len(strKrit) > 1 Then
strMid = "<"
strLen = Len(strKrit) - 1
strKrit = Right(strKrit, strLen)

'check and convert to sql date US american format

If strDataFlag = 2 Then
strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Or strDataFlag = 3 Then
'nothing
End If


ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & " " & strMid & " " & strKrit

ElseIf Left(strKrit, 1) = ">" And Len(strKrit) > 1 Then
strMid = ">"
strLen = Len(strKrit) - 1
strKrit = Right(strKrit, strLen)

'check and convert to sql date US american format

If strDataFlag = 2 Then
strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Or strDataFlag = 3 Then
'nothing
End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & strMid & " " & strKrit

ElseIf Left(strKrit, 1) = "*" And Len(strKrit) = 1 Then
strMid = "LIKE"
strLen = Len(strKrit) - 1
strKrit = "'*'"

'check and convert to sql date US american format
'no conversion needed '*' does the job
'If strDataFlag = 1 Then
' strKrit = sqlDate(strKrit)
'ElseIf strDataFlag = 0 Then
' 'nothing
'End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & " " & strMid & " " & strKrit

ElseIf Right(strKrit, 1) = "*" And Len(strKrit) > 1 Then
'MsgBox (Right(strKrit, 1))
strMid = "LIKE"
strKrit = Left(strKrit, strLen - 1)
strKrit = "'" & strKrit & "*" & "'"

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & " " & strMid & " " & strKrit

Else:
strMid = " = "
If strDataFlag = 2 Then
'strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Then
'nothing
ElseIf strDataFlag = 3 Then
strKrit = "'" & strKrit & "'"
End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & strMid & " " & strKrit

End If

If tempCounter < counter Then

ystrSQL = ystrSQL & " AND "
Else: 'nothing ystrSQL = ystrSQL & ";"
' ystrSQL = ystrSQL & ";"
End If

End If
Else
'nothing
End If

strKrit = ""

Next k

'strSQL = ystrSQL & ";"
ystrSQL = ystrSQL & " AND inactive = false"
strSQL = ystrSQL & ";"
'''''''''''''''''''''''''''''''''''MsgBox ("Final SQL: " & strSQL)
'strSQL = "Select * from tabTripHeader where Date = #7/24/2007#;" 'to be
deleted - trial
On Error GoTo err_handler
'MsgBox (strSQL)
Set rs = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)

If rs.RecordCount > 0 And xstrSQL <> ystrSQL Then

Forms(strmainform).RecordSource = strSQL
Call AttachForm(strmainform, strSQL)
Call bind(strmainform)
Else

' MsgBox ("No matches - please refine search"), vbInformation

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "No search results, please narrow your search" ' Define message.
Style = vbInformation ' Define buttons.
Title = "Search Result" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

'If Response = vbYes Then ' User chose Yes.
' MyString = "Yes" ' Perform some action.
'Else ' User chose No.
' MyString = "No" ' Perform some action.
'End If

End If


err_handler:

End Function


---------------------------------------------------------------------------------------
Is this the right way forward? I still find it's quite a bit of code for a
small thing like full screen text search. Isn't there an easier way? I kind
of like the approach of unbound forms as it speeds up access to tremendously
especially in a wlan environment.

Would you know by any chance how access works when using permanently linked
forms? Does access download all the table content or just what you see on the
screen?

Rgds

 

[Marshall Barton]

Thank you for your explanation. I wrote a routine to create the sql string
like this:


----------------------------------------------------------------------------------------------
Public Function SearchForm(strmainform, strTabSource)

strMid = ""
strKrit = ""
strSQL = ""

Set db = DBEngine(0)(0)
Dim rs As Recordset
Dim tempDate As String

Dim ystrSQL As String
Dim xstrSQL As String
ystrSQL = "select * from "
ystrSQL = ystrSQL & strTabSource & " "
'xstrSQL check SQL string to see if data has been found
xstrSQL = ystrSQL
Dim k As Integer
Dim tempCounter As Integer
tempCounter = 0
Dim counter As Integer
counter = 0
Dim tempStr As String

'select fields with tag 1 or 2, 1 for data and 2 for date format
'check if data is entered, no data ---> counter = 0, data entry yes ---->
counter > 0

tempCounter = 0

For k = 0 To Forms(strmainform).Controls.Count - 1
If Forms(strmainform).Controls(k).Tag = 1 Or
Forms(strmainform).Controls(k).Tag = 2 Or Forms(strmainform).Controls(k).Tag
= 3 Then
If Forms(strmainform).Controls(k) <> "" Then
counter = counter + 1
End If
End If
Next k

If counter > 0 Then
ystrSQL = ystrSQL & "where "
Else

'Dim Msg, Style, Title, Help, Ctxt, Response, MyString
'Msg = "No entries! Please enter data." ' Define message.
'Style = vbInformation ' Define buttons.
'Title = "Search Result" ' Define title.
'Help = "DEMO.HLP" ' Define Help file.
'Ctxt = 1000 ' Define topic
' context.
' Display message.
'Response = MsgBox(Msg, Style, Title, Help, Ctxt)

End If

For k = 0 To Forms(strmainform).Controls.Count - 1

If Forms(strmainform).Controls(k).Tag = 1 Or
Forms(strmainform).Controls(k).Tag = 2 Or Forms(strmainform).Controls(k).Tag
= 3 Then

If Forms(strmainform).Controls(k) <> "" Then
tempCounter = tempCounter + 1
strMid = ""

'evaluate numerical, date, text

strDataFlag = 0
'1 = numerical (0...9)
'2 = date (07/25/2007)
'3 = text (A....Z, 0....9)
'4 = ...

If Forms(strmainform).Controls(k).Tag = 1 Then
strDataFlag = 1 'H001, T001...
ElseIf Forms(strmainform).Controls(k).Tag = 2 Then
strDataFlag = 2 'date 07/25/2007
ElseIf Forms(strmainform).Controls(k).Tag = 3 Then
strDataFlag = 3 'text
End If

strKrit = Forms(strmainform).Controls(k)
strLen = Len(strKrit)

'evaluate strMid for SQL query

'option strMid definition
' case -----> strMid
' nothing -----> "="
' "<>" -----> "IS NOT"
' ">" -----> ">"
' "<" -----> "<"
' ">0" -----> "LIKE"
' "*" -----> "LIKE"

strLen = Len(strKrit)

If Left(strKrit, 2) = "<>" Then 'And Len(strKrit) > 2 Then
strMid = "<>"
strLen = Len(strKrit) - 2
strKrit = Right(strKrit, strLen)
MsgBox ("strKrit : " & strKrit)
'check and convert to sql date US American format

If strDataFlag = 2 Then
strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Then
'nothing, don't include them into SQL string
ElseIf strDataFlag = 3 Then
'...to follow

End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & strMid & " " & strKrit


ElseIf Left(strKrit, 1) = "<" And Len(strKrit) > 1 Then
strMid = "<"
strLen = Len(strKrit) - 1
strKrit = Right(strKrit, strLen)

'check and convert to sql date US american format

If strDataFlag = 2 Then
strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Or strDataFlag = 3 Then
'nothing
End If


ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & " " & strMid & " " & strKrit

ElseIf Left(strKrit, 1) = ">" And Len(strKrit) > 1 Then
strMid = ">"
strLen = Len(strKrit) - 1
strKrit = Right(strKrit, strLen)

'check and convert to sql date US american format

If strDataFlag = 2 Then
strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Or strDataFlag = 3 Then
'nothing
End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & strMid & " " & strKrit

ElseIf Left(strKrit, 1) = "*" And Len(strKrit) = 1 Then
strMid = "LIKE"
strLen = Len(strKrit) - 1
strKrit = "'*'"

'check and convert to sql date US american format
'no conversion needed '*' does the job
'If strDataFlag = 1 Then
' strKrit = sqlDate(strKrit)
'ElseIf strDataFlag = 0 Then
' 'nothing
'End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & " " & strMid & " " & strKrit

ElseIf Right(strKrit, 1) = "*" And Len(strKrit) > 1 Then
'MsgBox (Right(strKrit, 1))
strMid = "LIKE"
strKrit = Left(strKrit, strLen - 1)
strKrit = "'" & strKrit & "*" & "'"

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & " " & strMid & " " & strKrit

Else:
strMid = " = "
If strDataFlag = 2 Then
'strKrit = sqlDate(strKrit)
ElseIf strDataFlag = 1 Then
'nothing
ElseIf strDataFlag = 3 Then
strKrit = "'" & strKrit & "'"
End If

ystrSQL = ystrSQL &
Forms(strmainform).Controls(k).Name & strMid & " " & strKrit

End If

If tempCounter < counter Then

ystrSQL = ystrSQL & " AND "
Else: 'nothing ystrSQL = ystrSQL & ";"
' ystrSQL = ystrSQL & ";"
End If

End If
Else
'nothing
End If

strKrit = ""

Next k

'strSQL = ystrSQL & ";"
ystrSQL = ystrSQL & " AND inactive = false"
strSQL = ystrSQL & ";"
'''''''''''''''''''''''''''''''''''MsgBox ("Final SQL: " & strSQL)
'strSQL = "Select * from tabTripHeader where Date = #7/24/2007#;" 'to be
deleted - trial
On Error GoTo err_handler
'MsgBox (strSQL)
Set rs = db.OpenRecordset(strSQL, DB_OPEN_DYNASET)

If rs.RecordCount > 0 And xstrSQL <> ystrSQL Then

Forms(strmainform).RecordSource = strSQL
Call AttachForm(strmainform, strSQL)
Call bind(strmainform)
Else

' MsgBox ("No matches - please refine search"), vbInformation

Dim Msg, Style, Title, Help, Ctxt, Response, MyString

Msg = "No search results, please narrow your search" ' Define message.
Style = vbInformation ' Define buttons.
Title = "Search Result" ' Define title.
Help = "DEMO.HLP" ' Define Help file.
Ctxt = 1000 ' Define topic
' context.
' Display message.
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

'If Response = vbYes Then ' User chose Yes.
' MyString = "Yes" ' Perform some action.
'Else ' User chose No.
' MyString = "No" ' Perform some action.
'End If

End If


err_handler:

End Function


---------------------------------------------------------------------------------------
Is this the right way forward? I still find it's quite a bit of code for a
small thing like full screen text search. Isn't there an easier way? I kind
of like the approach of unbound forms as it speeds up access to tremendously
especially in a wlan environment.

Would you know by any chance how access works when using permanently linked
forms? Does access download all the table content or just what you see on the
screen?

Sorry, but I can not unravel all that. About all I can say
is that, yes, it is a lot for code, especially when you go
to all that trouble to loop through the controls collection
twice. Didn't you have to do something similar for the
unbound version of the form?

You might want to explore using the BuildCriteria function
instead of trying to parse out parts of the criteria.

You seem to be doing something with a record set in there
and I just don't comprehend what that would have to do with
setting the form's record source.

Maybe I failed to grasp what you are really doing and have
inadvertantly led you astray.

 

[Bernie]

Dear Marshall

Thank you for your reply. This rather long routine does the following:

1. parsing through the form's fields
2. evaluating type of field (text, numeric, date)
3. build a dynamic sql string (search string) based upon the user's form
entries like select all from table horse where horseid like "H0*" or date >
'01.01.2005'"

The main advantage of this routine is that the user can search with several
entries, full text search if you like.

Once I have the sql string, then I bind the form and link the dynamically
created sql string to the form's record source and have ultimate and very
fast searches.

Allen Browne uses a different approach by filtering. My approach is
basically the same but far more complicated in terms of vba coding but again,
perhaps there is an easier way of doing all this.

Rgds

 

[Bernie]

Dear Marshall

To answer the question of binding the forms, yes, I also use a routine to
bind the controls and of course the form's record source to the dynamically
created sql string. Here is the code:

Public Function displayVarform(strmainform, strSubformcontrol,
strControlname, strChildFields, strMasterFields, strRecordsource)

Call bindvarform(strmainform, strSubformcontrol)
Forms(strmainform)(strSubformcontrol).SourceObject = (strControlname)
Forms(strmainform)(strSubformcontrol).SourceObject = (strControlname)
Forms(strmainform)(strSubformcontrol).Form.RecordSource = (strRecordsource)

On Error Resume Next
Forms(strmainform)(strSubformcontrol).LinkMasterFields = strMasterFields
On Error Resume Next
Forms(strmainform)(strSubformcontrol).LinkChildFields = strChildFields

End Function
Public Function DisplaySubVarform(strmainform, strSubformcontrol,
strSubformControl1, strControlname, strChildFields, strMasterFields,
strRecordsource)


MsgBox ("Rec Source: " & strRecordsource)
MsgBox ("strControlName: " & strControlname)

Forms(strmainform)(strSubformcontrol)(strSubformControl1).SourceObject =
(strControlname)

Forms(strmainform)(strSubformcontrol)(strSubformControl1).Form.RecordSource =
(strRecordsource)


Forms(strmainform)(strSubformcontrol)(strSubformControl1).Form.AllowAdditions
= True

'On Error Resume Next

'Forms(strMainForm)(strSubformControl)(strSubformControl1).LinkMasterFields =
strMasterFields
'On Error Resume Next

'Forms(strMainForm)(strSubformControl)(strSubformControl1).LinkChildFields =
strChildFields

End Function
Public Function displayVarformUnlinked(strmainform, strSubformcontrol,
strControlname)

Forms(strmainform)(strSubformcontrol).SourceObject = (strControlname)

End Function
Public Function blank_fields(strForm)

Dim i As Integer

For i = 0 To Forms(strForm).Controls.Count - 1

If Forms(strForm).Controls(i).Tag = 1 Or Forms(strForm).Controls(i).Tag
= 2 Then
Forms(strForm).Controls(i).ControlSource = Forms(strForm).Controls(i).Name
Else
End If

Next i
End Function
Public Function unbound(strForm)

Dim i As Integer

For i = 0 To Forms(strForm).Controls.Count - 1

Select Case Forms(strForm).Controls(i).Tag

Case 1, 2, 3
Forms(strForm).Controls(i).ControlSource = ""

End Select

Next i

End Function
Public Function unboundvarform(strForm, strControlForm)

Dim i As Integer

For i = 0 To Forms(strForm)(strControlForm).Controls.Count - 1

Select Case Forms(strForm)(strControlForm).Controls(i).Tag

Case 1, 2, 3
Forms(strForm)(strControlForm).Controls(i).ControlSource = ""
End Select

Next i

End Function
Public Function UnboundSubVarform(strForm, strControlForm, strControlForm1)

Dim i As Integer

For i = 0 To Forms(strForm)(strControlForm)(strControlForm1).Controls.Count
- 1

Select Case Forms(strForm)(strControlForm)(strControlForm1).Controls(i).Tag

Case 1, 2, 3

Forms(strForm)(strControlForm)(strControlForm1).Controls(i).ControlSource = ""
End Select

Next i

End Function
Public Function clearfields(strForm)

Dim i As Integer

For i = 0 To Forms(strForm).Controls.Count - 1

Select Case Forms(strForm).Controls(i).Tag

Case 1, 2, 3
Forms(strForm).Controls(i) = ""
End Select

Next i

End Function
Public Function detach(strForm)

Dim i As Integer

Forms(strForm).RecordSource = ""

End Function
Public Function bind(strForm)

Dim i As Integer

For i = 0 To Forms(strForm).Controls.Count - 1

Select Case Forms(strForm).Controls(i).Tag

Case 1, 2, 3
Forms(strForm).Controls(i).ControlSource = Forms(strForm).Controls(i).Name
End Select

Next i

End Function
Public Function AttachForm(strForm, strRecordsource)

Forms(strForm).RecordSource = (strRecordsource)

End Function
Public Function DetachVarform(strmainform, strSubformcontrol, strControlname)

Forms(strmainform)(strSubformcontrol).Form.RecordSource = ""

End Function
Public Function DetachSubVarform(strmainform, strSubformcontrol,
strSubformControl1, strControlname)

Forms(strmainform)(strSubformcontrol)(strSubformControl1).Form.RecordSource
= ""

End Function
Public Function ClearFieldsVarform(strmainform, strSubformcontrol)

Dim i As Integer

For i = 0 To Forms(strmainform)(strSubformcontrol).Controls.Count - 1

Select Case Forms(strmainform)(strSubformcontrol).Controls(i).Tag

Case 1, 2, 3
Forms(strmainform)(strSubformcontrol).Controls(i) = ""
End Select

Next i

End Function
Public Function ClearFieldsSubVarform(strmainform, strSubformcontrol,
strSubformControl1)

Dim i As Integer

For i = 0 To
Forms(strmainform)(strSubformcontrol)(strSubformControl1).Controls.Count - 1

Select Case
Forms(strmainform)(strSubformcontrol)(strSubformControl1).Controls(i).Tag

Case 1, 2, 3
Forms(strmainform)(strSubformcontrol)(strSubformControl1).Controls(i) = ""
End Select

Next i

End Function
Public Function bindvarform(strmainform, strSubformcontrol)

Dim i As Integer

For i = 0 To Forms(strmainform)(strSubformcontrol).Controls.Count - 1

Select Case Forms(strmainform)(strSubformcontrol).Controls(i).Tag

Case 1, 2, 3

Forms(strmainform)(strSubformcontrol).Controls(i).ControlSource
= Forms(strmainform)(strSubformcontrol).Controls(i).Name

End Select

Next i

End Function

 

[Marshall Barton]

To answer the question of binding the forms, yes, I also use a routine to
bind the controls and of course the form's record source to the dynamically
created sql string. Here is the code:

[snip]

My goodness, that is a lot of work. Why not bind the form
controls in design view instead of all that? At least, you
would then only need to set the form's record source.

I use an approach that is very similar to Alan Browne's.
If I want to get fancy with comparison operators and compund
conditions, then I use BuildCriteria instead of trying to
figure it all out in code and construction the criteria
expression myself.

I just don't understand why you think your situation is so
much more complex.

 

[Bernie]

Dear Marshall

I reworked my routines and am now using the buildCriteria expression which
works much better than my awfully long code. The only problem I'm having is
with the subforms. I want to write a full-text search form for the main form
and all the various subforms. Perhaps anyone in here has done this before,
who knows.

Any comments again are most appreciated.

Rgds



Option Compare Database

Public Sub Blank_Click()

strMainform = Me.Name
Call blank_mainform(strMain)
strVarform = "varform1"
Call blank_varform(strMainform, strVarform)
strVarform = "varform2"
Call blank_varform(strMainform, strVarform)

End Sub

Private Sub cmdListProperties_Click()
ListControlProps Me
End Sub

Private Sub cmdSearch_Click()

Call searchMainForm(strMainform)

End Sub

Private Sub Command50_Click()

Me.RecordSource = "customers"

End Sub

Public Sub bind(strForm)

'bind

'Loop through each control on the form to unbind.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acTextBox
ctl.ControlSource = ctl.Name
Case acComboBox
ctl.ControlSource = ctl.Name
End Select
End With
Next ctl
End Sub

Public Sub ListControlProps(ByRef frm As Form)
Dim ctl As Control
Dim prp As Property

On Error GoTo props_err

For Each ctl In frm.Controls

Debug.Print ctl.Properties("Name")
For Each prp In ctl.Properties
Debug.Print vbTab & prp.Name & " = " & prp.Value
Next prp
Next ctl

props_exit:
Set ctl = Nothing
Set prp = Nothing
Exit Sub

props_err:
If Err = 2187 Then
Debug.Print vbTab & prp.Name & " = Only available at design time."
Resume Next
Else
Debug.Print vbTab & prp.Name & " = Error Occurred: " & Err.Description
Resume Next
End If
End Sub

Private Sub Command60_Click()
On Error GoTo Err_Command60_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Command60_Click:
Exit Sub

Err_Command60_Click:
MsgBox Err.Description
Resume Exit_Command60_Click

End Sub
Private Sub Orders_Click()

strMainform = "customers"
strVarform = "varform"
strSourceObject = "frmSubOrders"
strChildFields = "customerid"
strMasterFields = "customerid"
RecordSource = "orders"

Me!Varform.SourceObject = "frmSubOrders"
Me!Varform.Form.RecordSource = "orders"
'Me.RecordSource = sSQL & sWhereClause

'Forms!customers!Varform.Form.RecordSource = "orders"
----------> problem
'Forms!customers!Varform.SourceObject = "frmsuborders"

'Forms!customers!Varform.LinkMasterFields = strMasterFields
'Forms!customers!Varform.LinkChildFields = strChildFields

'Forms(strmainform)(strVarform).LinkMasterFields = strMasterFields
'Forms(strmainform)(strVarform).LinkChildFields = strChildFields
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
End Sub

Private Sub Orders1_Click()

strMasterFields = "customerid"
strChildFields = "customerid"

'Me!varform1.SourceObject = "frmSubOrders1"
Me!varform1.LinkMasterFields = strMasterFields
Me!varform1.LinkChildFields = strChildFields

Me.varform1.Visible = True
Me.varform2.Visible = False

End Sub
Private Sub Orders2_Click()

strMasterFields = "customerid"
strChildFields = "customerid"

'Me!varform2.SourceObject = "frmSubOrders2"
Me!varform2.LinkMasterFields = strMasterFields
Me!varform2.LinkChildFields = strChildFields

Me.varform1.Visible = False
Me.varform2.Visible = True

End Sub
Public Function searchMainForm(strMainform)
On Error Resume Next

Dim tempSTR As String
Dim ctl As Control
Dim sSQL As String
Dim sWhereClause As String

'Initialize the Where Clause variable.
sWhereClause = " Where "

'Start the first part of the select statement.
sSQL = "select * from customers "

'Loop through each control on the form to get its value.
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.

Select Case .ControlType

Case .acTextBox
.SetFocus
'This is the function that actually builds
'the clause.

If .Tag = 1 Then

If sWhereClause = " Where " Then
sWhereClause = sWhereClause &
BuildCriteria(.Name, dbLong, .Text)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbLong, .Text)
End If

ElseIf .Tag = 2 Then

If sWhereClause = " Where " Then
sWhereClause = sWhereClause &
BuildCriteria(.Name, dbText, .Text)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbText, .Text)
End If

ElseIf .Tag = 3 Then

If sWhereClause = " Where " Then
sWhereClause = sWhereClause &
BuildCriteria(.Name, dbDate, .Text)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbDate, .Text)
End If
End If

Case .acComboBox
.SetFocus
'This is the function that actually builds
'the clause.

'If .Tag = 1 Then

If sWhereClause = " Where " Then
sWhereClause = sWhereClause &
BuildCriteria(.Name, dbLong, .Text)
Else
sWhereClause = sWhereClause & " and " &
BuildCriteria(.Name, dbLong, .Text)
End If
'End If
End Select
End With
Next ctl


'Set the forms recordsource equal to the new
'select statement.
''''''''''''''''''Me.txtSQL = sSQL & sWhereClause

Me.Form.RecordSource = ""
Me.Form.RecordSource = sSQL & sWhereClause
tempSTR = Me.Form.RecordSource
Label57.Caption = tempSTR
Call bind(Me.Name)
Me.Requery

Set db = DBEngine(0)(0)
Dim rs As Recordset
Set rs = db.OpenRecordset(sSQL & sWhereClause)
rs.MoveLast
''''''''''''''''''''''''''MsgBox (rs.RecordCount)
Me.AllowAdditions = False
If rs.RecordCount < 1 Then
Call Blank_Click
Me.Form.RecordSource = ""
Call Blank_Click
MsgBox ("No Seach Results")

End If
End Function
Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
Public Function blank_mainform(strMainform)

'unbind

Forms(strMainform).RecordSource = ""

'Loop through each control on the form to unbind.'
For Each ctl In Me.Controls
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acTextBox
ctl.ControlSource = ""
ctl = Null
Case acComboBox
ctl.ControlSource = ""
ctl = ""
End Select
End With
Next ctl

End Function
Public Function blank_varform(strMainform, strVarform)

'unbind

Forms(strMainform)(strVarform).Form.RecordSource = ""

'Loop through each control on the form to unbind.'
For Each ctl In Forms(strMainform)(strVarform).Controls
'MsgBox (ctl.Name)
With ctl
'The only Control you are using is the text box.
'However, you can add as many types of controls as you want.
Select Case .ControlType
Case acTextBox
ctl.ControlSource = ""
ctl = "1.1.2007"
Case acComboBox
ctl.ControlSource = ""
ctl = "1.1.2007"
End Select
End With
Next ctl

End Function

 

[Bernie]

....I forgot to add the code in the module. Here it is.


Option Compare Database

Global strMainform As String
Global strVarform As String
Global strSourceObject As String
Global strChildFields As String
Global strMasterFields As String
Global RecordSource As String
Public Function bindvarform(strMainform, strVarform)

Dim i As Integer

For i = 0 To Forms(strMainform)(strVarform).Controls.Count - 1

Select Case Forms(strMainform)(strVarform).Controls(i).Tag

Case 1, 2, 3

Forms(strMainform)(strVarform).Controls(i).ControlSource =
Forms(strMainform)(strVarform).Controls(i).Name

End Select

Next i

End Function
Public Function DisplayVarform(strMainform, strVarform, frmSubOrders,
strChildFields, strMasterFields, strRecordsource)

Forms(strMainform)(strVarform).SourceObject = (frmSubOrders)
Forms(strMainform)(strVarform).Form.RecordSource = (strRecordsource)

End Function