Sinlge Form to Add/View/Search Records

  • Thread starter Thread starter Simon
  • Start date Start date
S

Simon

Good Afternoon,
I require some assistance with the best practice for the following scenario

I have a form frmPerson and a table frmPersonSearch.
frmPerson is bound to a table recordsource
frmPersonSearch is unbound and has 2 textbox controls
I perform 3 functions with these 2 forms: add new person, view (edit)
existing person and search for person.

When I click the Add button, I open the frmPerson form with the allow new
entries only.
When I click the View button, I open the frmPerson form as standard - I can
scroll through all records.
When I click the Search button, I open the frmPersonSearch form which
queries the recordsource of frmPerson and in turn the query is use to filter
the frmPerson form which is then displayed.

I would like to use the same form to provide all 3 functionalities, but the
issue I can see is that all fields would have to be unbound to perform the
searching and then bound again to show the results.
Am I correct?
Is there a better way to do this?

Thanks
Simon
 
I always use one form for all activities.
To work as a search, use an unbound combo to find a record and make it the
current record. Here is a sample from one of my forms:

Here is how you look up a record and make it the current record:

Private Sub cboClientID_AfterUpdate()
On Error GoTo cboClientID_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboClientID.Column(0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


cboClientID_AfterUpdate_Exit:

Exit Sub
On Error GoTo 0

cboClientID_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_AfterUpdate of VBA Document Form_Form2"
GoTo cboClientID_AfterUpdate_Exit
End Sub

In the AfterUpdate event of the combo, you can allow adding of new records:

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboClientID_NotInList_Error

If MsgBox("Client " & NewData & " Not Found" & vbNewLine & _
"Add This Client?", vbYesNo + vbQuestion, "Add Client") = vbYes
Then
DoCmd.GoToRecord , , acNewRec
Me.txtMainName = NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

cboClientID_NotInList_Exit:

Exit Sub
On Error GoTo 0

cboClientID_NotInList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_NotInList of VBA Document Form_Form2"
GoTo cboClientID_NotInList_Exit
End Sub


To add a new record, use a command button that creates a new record.
 
Thanks for your answer Dave

What I should have included within my original post, was that the
frmPersonSearch form has 2 text boxes (name and address) which is the user
can enter data. A query will then filter several table fields (e.g.
FirstName, LastNme, etc) which make up the full name and address information.

I would like to be able to use all text box controls on the frmPerson form
within the search query, but obviously they are all bound to the
recordsource. So each time the user clicks on the search button, all controls
would have to be unbound, then bound again upon displaying the results. To me
this just seams awkward, with all the binding and unbinding that goes on, but
what other alternatives are there?

Btw, I like your error controls - no issues knowing where an error comes from.

Thanks
Simon

Klatuu said:
I always use one form for all activities.
To work as a search, use an unbound combo to find a record and make it the
current record. Here is a sample from one of my forms:

Here is how you look up a record and make it the current record:

Private Sub cboClientID_AfterUpdate()
On Error GoTo cboClientID_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboClientID.Column(0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


cboClientID_AfterUpdate_Exit:

Exit Sub
On Error GoTo 0

cboClientID_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_AfterUpdate of VBA Document Form_Form2"
GoTo cboClientID_AfterUpdate_Exit
End Sub

In the AfterUpdate event of the combo, you can allow adding of new records:

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboClientID_NotInList_Error

If MsgBox("Client " & NewData & " Not Found" & vbNewLine & _
"Add This Client?", vbYesNo + vbQuestion, "Add Client") = vbYes
Then
DoCmd.GoToRecord , , acNewRec
Me.txtMainName = NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

cboClientID_NotInList_Exit:

Exit Sub
On Error GoTo 0

cboClientID_NotInList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_NotInList of VBA Document Form_Form2"
GoTo cboClientID_NotInList_Exit
End Sub


To add a new record, use a command button that creates a new record.


--
Dave Hargis, Microsoft Access MVP


Simon said:
Good Afternoon,
I require some assistance with the best practice for the following scenario

I have a form frmPerson and a table frmPersonSearch.
frmPerson is bound to a table recordsource
frmPersonSearch is unbound and has 2 textbox controls
I perform 3 functions with these 2 forms: add new person, view (edit)
existing person and search for person.

When I click the Add button, I open the frmPerson form with the allow new
entries only.
When I click the View button, I open the frmPerson form as standard - I can
scroll through all records.
When I click the Search button, I open the frmPersonSearch form which
queries the recordsource of frmPerson and in turn the query is use to filter
the frmPerson form which is then displayed.

I would like to use the same form to provide all 3 functionalities, but the
issue I can see is that all fields would have to be unbound to perform the
searching and then bound again to show the results.
Am I correct?
Is there a better way to do this?

Thanks
Simon
Click to expand...
Click to expand...
 
I can't imagine wanting to search on every field in the table. I can see
using a few different ones and in combinations, but you wouldn't do that with
your bound controls.

You can always put unbound combo boxes on the form header based on the
fields you want to search. Then use a command button to execute the search.

The code in the button's click event can be written to build filter criteria
based on which combos have a selection. Then set the form's filter using the
criteria.
--
Dave Hargis, Microsoft Access MVP


Simon said:
Thanks for your answer Dave

What I should have included within my original post, was that the
frmPersonSearch form has 2 text boxes (name and address) which is the user
can enter data. A query will then filter several table fields (e.g.
FirstName, LastNme, etc) which make up the full name and address information.

I would like to be able to use all text box controls on the frmPerson form
within the search query, but obviously they are all bound to the
recordsource. So each time the user clicks on the search button, all controls
would have to be unbound, then bound again upon displaying the results. To me
this just seams awkward, with all the binding and unbinding that goes on, but
what other alternatives are there?

Btw, I like your error controls - no issues knowing where an error comes from.

Thanks
Simon

Klatuu said:
I always use one form for all activities.
To work as a search, use an unbound combo to find a record and make it the
current record. Here is a sample from one of my forms:

Here is how you look up a record and make it the current record:

Private Sub cboClientID_AfterUpdate()
On Error GoTo cboClientID_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboClientID.Column(0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


cboClientID_AfterUpdate_Exit:

Exit Sub
On Error GoTo 0

cboClientID_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_AfterUpdate of VBA Document Form_Form2"
GoTo cboClientID_AfterUpdate_Exit
End Sub

In the AfterUpdate event of the combo, you can allow adding of new records:

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboClientID_NotInList_Error

If MsgBox("Client " & NewData & " Not Found" & vbNewLine & _
"Add This Client?", vbYesNo + vbQuestion, "Add Client") = vbYes
Then
DoCmd.GoToRecord , , acNewRec
Me.txtMainName = NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

cboClientID_NotInList_Exit:

Exit Sub
On Error GoTo 0

cboClientID_NotInList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_NotInList of VBA Document Form_Form2"
GoTo cboClientID_NotInList_Exit
End Sub


To add a new record, use a command button that creates a new record.


--
Dave Hargis, Microsoft Access MVP


Simon said:
Good Afternoon,
I require some assistance with the best practice for the following scenario

I have a form frmPerson and a table frmPersonSearch.
frmPerson is bound to a table recordsource
frmPersonSearch is unbound and has 2 textbox controls
I perform 3 functions with these 2 forms: add new person, view (edit)
existing person and search for person.

When I click the Add button, I open the frmPerson form with the allow new
entries only.
When I click the View button, I open the frmPerson form as standard - I can
scroll through all records.
When I click the Search button, I open the frmPersonSearch form which
queries the recordsource of frmPerson and in turn the query is use to filter
the frmPerson form which is then displayed.

I would like to use the same form to provide all 3 functionalities, but the
issue I can see is that all fields would have to be unbound to perform the
searching and then bound again to show the results.
Am I correct?
Is there a better way to do this?

Thanks
Simon
Click to expand...
Click to expand...
Click to expand...
 
Granted, not every field in the table is likely to be searched on, but if
there was a way to simply ‘convert’ the form’s controls to unbound and then
back agian, I may have used it.

I shall recreate the controls from the frmPersonSearch form to the frmPerson
form and then I can just port the current filtering criteria onto the new
unbound controls.

Thanks
Simon


Klatuu said:
I can't imagine wanting to search on every field in the table. I can see
using a few different ones and in combinations, but you wouldn't do that with
your bound controls.

You can always put unbound combo boxes on the form header based on the
fields you want to search. Then use a command button to execute the search.

The code in the button's click event can be written to build filter criteria
based on which combos have a selection. Then set the form's filter using the
criteria.
--
Dave Hargis, Microsoft Access MVP


Simon said:
Thanks for your answer Dave

What I should have included within my original post, was that the
frmPersonSearch form has 2 text boxes (name and address) which is the user
can enter data. A query will then filter several table fields (e.g.
FirstName, LastNme, etc) which make up the full name and address information.

I would like to be able to use all text box controls on the frmPerson form
within the search query, but obviously they are all bound to the
recordsource. So each time the user clicks on the search button, all controls
would have to be unbound, then bound again upon displaying the results. To me
this just seams awkward, with all the binding and unbinding that goes on, but
what other alternatives are there?

Btw, I like your error controls - no issues knowing where an error comes from.

Thanks
Simon

Klatuu said:
I always use one form for all activities.
To work as a search, use an unbound combo to find a record and make it the
current record. Here is a sample from one of my forms:

Here is how you look up a record and make it the current record:

Private Sub cboClientID_AfterUpdate()
On Error GoTo cboClientID_AfterUpdate_Error

With Me.RecordsetClone
.FindFirst "[ClientID] = " & Me.cboClientID.Column(0)
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With


cboClientID_AfterUpdate_Exit:

Exit Sub
On Error GoTo 0

cboClientID_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_AfterUpdate of VBA Document Form_Form2"
GoTo cboClientID_AfterUpdate_Exit
End Sub

In the AfterUpdate event of the combo, you can allow adding of new records:

Private Sub cboClientID_NotInList(NewData As String, Response As Integer)

On Error GoTo cboClientID_NotInList_Error

If MsgBox("Client " & NewData & " Not Found" & vbNewLine & _
"Add This Client?", vbYesNo + vbQuestion, "Add Client") = vbYes
Then
DoCmd.GoToRecord , , acNewRec
Me.txtMainName = NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

cboClientID_NotInList_Exit:

Exit Sub
On Error GoTo 0

cboClientID_NotInList_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboClientID_NotInList of VBA Document Form_Form2"
GoTo cboClientID_NotInList_Exit
End Sub


To add a new record, use a command button that creates a new record.


--
Dave Hargis, Microsoft Access MVP


:

Good Afternoon,
I require some assistance with the best practice for the following scenario

I have a form frmPerson and a table frmPersonSearch.
frmPerson is bound to a table recordsource
frmPersonSearch is unbound and has 2 textbox controls
I perform 3 functions with these 2 forms: add new person, view (edit)
existing person and search for person.

When I click the Add button, I open the frmPerson form with the allow new
entries only.
When I click the View button, I open the frmPerson form as standard - I can
scroll through all records.
When I click the Search button, I open the frmPersonSearch form which
queries the recordsource of frmPerson and in turn the query is use to filter
the frmPerson form which is then displayed.

I would like to use the same form to provide all 3 functionalities, but the
issue I can see is that all fields would have to be unbound to perform the
searching and then bound again to show the results.
Am I correct?
Is there a better way to do this?

Thanks
Simon
Click to expand...
Click to expand...
Click to expand...
 
I would like to be able to use all text box controls on the frmPerson form
within the search query,
Click to expand...

Have you intentionally rejected the builtin "Query By Form" feature? It's
limited, but it may get you part of the way.
 
John,

Admittingly this is not something that I am familiar with explicitly, but
after looking at the MS link http://support.microsoft.com/kb/304428 it
appears to be very similar to what I have. It suggests using a form based on
the table with additional unbound controls to perform the searching, which is
what Dave suggested.

I think I shall change my setup from having the second form for search data
input and move the input controls to the main display form.

Still, it would be nice to use the original form (all text box controls),
just unbinding for searching records and binding for displaying/editing
records.

Thanks
Simon
 
Admittingly this is not something that I am familiar with explicitly, but
after looking at the MS link http://support.microsoft.com/kb/304428 it
appears to be very similar to what I have. It suggests using a form based on
the table with additional unbound controls to perform the searching, which is
what Dave suggested.
Click to expand...

No, that's not what I'm suggesting.

Select Records... Filter by Form from the menu.

Give it a try and see if it meets your needs.
 
Back
Top