subform filter

[SuzyQ]

I have a subform that I set a filter on based on a date entered to an unbound
text box in the main form. It works fine except when there are no records in
the subform data within the range specified in the main form. When that
happens, a filter is set, but a new blank record is added. I don't want a
blank record to be added unless the user decides to add one. How can I
prevent this automatic insertion of a blank record when no records meet the
filter criteria?

 

[SuzyQ]

Is that the only way? I would rather the user use the * record on the screen
to enter data without having to take the extra step. I don't have an add
button anywhere else, and for the most part there are be records so the
filter does not create the blank record and when the user wants to add a
record. I don't want to cause the user to take an extra step for each add
when the scenario is the exception rather than the rule.

 

[John W. Vinson]

Is that the only way? I would rather the user use the * record on the screen
to enter data without having to take the extra step. I don't have an add
button anywhere else, and for the most part there are be records so the
filter does not create the blank record and when the user wants to add a
record. I don't want to cause the user to take an extra step for each add
when the scenario is the exception rather than the rule.

Whoa. Is the database creating AND SAVING a blank record? It shouldn't be. You
would normally have the *> blank new record line, but it shouldn't be actually
writing that record to disk. If it is, then there must be something in your
code or your macros that is doing so.

 

[SuzyQ]

Sorry, I forgot to include that I have code to check to see if the current
record is a new record, and if it is to auto fill in the date using the last
used date, but I only want that to happen if the user navigates to the new
record, not if it is selected because the user created a filter in which
there are no records.

 

[John W. Vinson]

Sorry, I forgot to include that I have code to check to see if the current
record is a new record, and if it is to auto fill in the date using the last
used date, but I only want that to happen if the user navigates to the new
record, not if it is selected because the user created a filter in which
there are no records.

Sorry my clairvoyance unit was down for maintenance... <g>

Please post your code. Checking Me.NewRecord probably won't help because if
you have a filter with no records, you WILL be at the new record; you'll
probably need to use a DLookUp or a recordset using the filter to see if there
are records being filtered out.

 

[SuzyQ]

Thanks for you help. I didn't use the DLookUp, but I think I solved it by
modifying my code below. Which way do you think is more efficient?

....
Else 'new record
Dim rs As Object 'ADDED THIS LINE
Set rs = Me.Recordset.Clone 'ADDED THIS LINE

If rs.RecordCount > 0 Then 'ADDED THIS LINE
If Not IsNull([Forms]![frmTimeSheet]!SaveDate) And _
[Forms]![frmTimeSheet]!txtLastEmployee = _
[Forms]![frmTimeSheet]!EmployeeNumber Then

'if there is a last used date and the current new record is
'for the same employee as the last record viewed then auto
fill fields

'automatically enter last used date to new record
Me.DateWorked = [Forms]![frmTimeSheet]!SaveDate
...


:

Sorry my clairvoyance unit was down for maintenance... <g>

And I thought you could read minds. I'm shattered.

 

[John W. Vinson]

Thanks for you help. I didn't use the DLookUp, but I think I solved it by
modifying my code below. Which way do you think is more efficient?

...
Else 'new record
Dim rs As Object 'ADDED THIS LINE
Set rs = Me.Recordset.Clone 'ADDED THIS LINE

If rs.RecordCount > 0 Then 'ADDED THIS LINE
If Not IsNull([Forms]![frmTimeSheet]!SaveDate) And _
[Forms]![frmTimeSheet]!txtLastEmployee = _
[Forms]![frmTimeSheet]!EmployeeNumber Then

'if there is a last used date and the current new record is
'for the same employee as the last record viewed then auto
fill fields

'automatically enter last used date to new record
Me.DateWorked = [Forms]![frmTimeSheet]!SaveDate
...

If it works, it looks fine to me. There doesn't seem to be anything in it to
ensure you're getting the "last" record though; does that matter?

 

[SuzyQ]

It's working perfectly. The last viewed is handled elsewhere, this code just
checks the fields and makes the decision whether or not to autofill it is
also no longer autofilling when there is a filter and no records match.
Thanks for you help.

Thanks for you help. I didn't use the DLookUp, but I think I solved it by
modifying my code below. Which way do you think is more efficient?

...
Else 'new record
Dim rs As Object 'ADDED THIS LINE
Set rs = Me.Recordset.Clone 'ADDED THIS LINE

If rs.RecordCount > 0 Then 'ADDED THIS LINE
If Not IsNull([Forms]![frmTimeSheet]!SaveDate) And _
[Forms]![frmTimeSheet]!txtLastEmployee = _
[Forms]![frmTimeSheet]!EmployeeNumber Then

'if there is a last used date and the current new record is
'for the same employee as the last record viewed then auto
fill fields

'automatically enter last used date to new record
Me.DateWorked = [Forms]![frmTimeSheet]!SaveDate
...

If it works, it looks fine to me. There doesn't seem to be anything in it to
ensure you're getting the "last" record though; does that matter?