Y
ym
Hi,
I posted a question yesterday and Squirrel had replied with the following
solution (thanx Squirrel). I have more questions on this. Squirrel, if you
are online, please respond or anyone who can help, please provide some
suggestion.
==> start of Squirrel's reply :
This is suggestion is really simple but might get you started. You say you
have a main form and subform with Link Child Fields and Link Master Fields
set.
Put an unbound combobox (cboDate) on the main form with this type of query
in the rowsource:
SELECT distinct DOB FROM Complete; (using my code - this is
DOB=date of birth), this is to get a list of all the unique dates in the
appropriate field in Complete table.
Private Sub cboDate_AfterUpdate()
Me.FilterOn = True
Me.Filter = "[DOB] = #" & Me![cboDate] & "#"
Me.Requery
End Sub
After the user selects a date, the above event will be triggered and the
main form will filter for all records with that date - you don't say whether
or not the date will be unique in your table, this will filter and retrieve
all records with the date selected in the combobox. If the main and subform
are appropriately linked then the subform will be automatically populated
with records from [Complete Detail]. The user will need to select the right
record in the main form (if more than one record with this date).
Also a command button (cmdRemoveFilter) on the main form with this event:
Private Sub cmdRemoveFilter_Click()
Me.FilterOn = False
Me.Requery
End Sub
When the user has finished with that record he should click the command
button to remove the filter and thus the main form will have all records
available.
If you expose the appropriate date field as a bound data entry field on the
main form, perhaps you could teach your users to select from the combobox if
the date is available, otherwise just enter the date into the bound textbox
and do regular data entry. Click the asterisk at the bottom of the main
form (where the previous/next selection lives) in the usual fashion to enter
a new record into the main form. If you have the Child/Master fields set up
properly then the subform will be ready for data entry and the [Complete ID]
field in the subform will be populated automatically. I'm assuming that the
[Complete ID] field in the main form is an autonumber and thus will be
populated when the user enters a new date, i.e. a new record.
This is a quick and easy way to get started and see how a main form/subform
work.
===> end of Squirrel's reply
Using the above strategy, when a selection is made on the cboDate combo box,
the equivalent [Complete Date] value gets displayed in the textbox. Note :
[Complete Date] is unique.
1) I use the cboDate to select and display associated child records in the
subform and in the subform, I am able to add and edit child records.
2) I use the textbox txtDate to enter a new date and to create this new
record, I Click the asterisk at the bottom of the main form (where the
previous/next selection lives) and the subform is available for new child
record entry.
3) How can I incorporate both functions Search Parent (edit/add children)
and New Parent (add children) by using just one cboDate - that is if a DATE
is entered in the combo cboDate, then I am creating a New Parent otherwise I
am searching for an existing parent. Instead of clicking the asterisk at the
bottom to create a new record, I would like to use code - how do I achieve
this ???
Thank you in advance for any help.
I posted a question yesterday and Squirrel had replied with the following
solution (thanx Squirrel). I have more questions on this. Squirrel, if you
are online, please respond or anyone who can help, please provide some
suggestion.
==> start of Squirrel's reply :
This is suggestion is really simple but might get you started. You say you
have a main form and subform with Link Child Fields and Link Master Fields
set.
Put an unbound combobox (cboDate) on the main form with this type of query
in the rowsource:
SELECT distinct DOB FROM Complete; (using my code - this is
DOB=date of birth), this is to get a list of all the unique dates in the
appropriate field in Complete table.
Private Sub cboDate_AfterUpdate()
Me.FilterOn = True
Me.Filter = "[DOB] = #" & Me![cboDate] & "#"
Me.Requery
End Sub
After the user selects a date, the above event will be triggered and the
main form will filter for all records with that date - you don't say whether
or not the date will be unique in your table, this will filter and retrieve
all records with the date selected in the combobox. If the main and subform
are appropriately linked then the subform will be automatically populated
with records from [Complete Detail]. The user will need to select the right
record in the main form (if more than one record with this date).
Also a command button (cmdRemoveFilter) on the main form with this event:
Private Sub cmdRemoveFilter_Click()
Me.FilterOn = False
Me.Requery
End Sub
When the user has finished with that record he should click the command
button to remove the filter and thus the main form will have all records
available.
If you expose the appropriate date field as a bound data entry field on the
main form, perhaps you could teach your users to select from the combobox if
the date is available, otherwise just enter the date into the bound textbox
and do regular data entry. Click the asterisk at the bottom of the main
form (where the previous/next selection lives) in the usual fashion to enter
a new record into the main form. If you have the Child/Master fields set up
properly then the subform will be ready for data entry and the [Complete ID]
field in the subform will be populated automatically. I'm assuming that the
[Complete ID] field in the main form is an autonumber and thus will be
populated when the user enters a new date, i.e. a new record.
This is a quick and easy way to get started and see how a main form/subform
work.
===> end of Squirrel's reply
Using the above strategy, when a selection is made on the cboDate combo box,
the equivalent [Complete Date] value gets displayed in the textbox. Note :
[Complete Date] is unique.
1) I use the cboDate to select and display associated child records in the
subform and in the subform, I am able to add and edit child records.
2) I use the textbox txtDate to enter a new date and to create this new
record, I Click the asterisk at the bottom of the main form (where the
previous/next selection lives) and the subform is available for new child
record entry.
3) How can I incorporate both functions Search Parent (edit/add children)
and New Parent (add children) by using just one cboDate - that is if a DATE
is entered in the combo cboDate, then I am creating a New Parent otherwise I
am searching for an existing parent. Instead of clicking the asterisk at the
bottom to create a new record, I would like to use code - how do I achieve
this ???
Thank you in advance for any help.