conditional filter on subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with names linked to a subform containing all that person's data. The subform is used for entering values based on the date. I would like to be able to enter a date and if a record with that date already exists, then edit the data and if not, add a new record with that date. How would I go about doing this?

I have combed websites and my reference books for any hints, but this (Access) is still new to me.

Thanks.
 
No vice,

First of all, I would suggest you shoud have some sort if ID number or
something in the main Person table, and also in the other table as a
linking field, which means you would not have the person's name in the
second table. Using people's names has been consistently found to not
be ideal as the basis of linking tables (and hence forms/suforms).
So, let's suppose you have done this.

OK, one approach, possibly a bit crude but effective, would be to put
a VBA procedure on the Before Update event of the textbox where you
enter the date. It will see if there is already an existing record
for this Person with the entered date, if not does nothing and you can
proceed, otherwise it cancels the record and tells you all about it.
The code could be done in several ways. Here's one...

If DCount("*","YourTable","[DateField]=#" & Me.DateField & _
"# And [PersonID]=" & Me.PersonID) > 0 Then
Me.Undo
MsgBox "Record already exists for this date"
End If

If you wanted to have the focus automatically move on the subform to
the existing record, well, this is do-able too, but would require some
slightly more complex code.

- Steve Schapel, Microsoft Access MVP
 
The form is linked to the subform with a user ID number - the main form just cycles through all the users. On the subform, I have a date picker button that puts the date into the Date field. For some reason, when I change the date, the rest of the information never changes and I typically get errors because Access thinks I'm trying to add values using the same date (I have two primary keys - the user ID and the date).

When I did as you suggested, I got the same results - nothing. Any suggestions?
 
Novice,

Is your subform in single view or continuous view? It really sounds
like you are changing the date on an existing record, rather than
entering to a new record. Would this be right?

- Steve Schapel, Microsoft Access MVP
 
The subform is currently set to single view.

I'm lost as to whether I need to make a query, an append query, find record, etc. to add values to my table by checking the date first. I only have two tables - a users table (PK user ID) and the values table (PKs user ID and date). Currently, the form opens and all records for a user are displayed - if I want to add another, I have to use the navigation buttons. I was hoping to just enter a date on the form or subform, whichever, then have some event occur which would search the records to see if that date already exists (so I don't create duplicates - date is one of the PKs) or let me add a new record with that date. I feel like I'm chasing my tail... any suggestions/clarifications/pointers would be greatly appreciated.
 
Novice,

Thanks for the further explanation. I am now starting to piece
together a picture of what you are doing.

Just by way of clarification of jargon, a table in Access can only
ever have one primary key. That primary key can be comprised of more
than one field, i.e. a composite key, which sounds like what you have
done. This is ok, but for the record, I would personally never design
it like this.

I already told you in my earlier reply a way to check for duplicate
dates. It assumes you will be entering the date into the date field
on a new record on the subform. Did you try my suggestion with a new
record? Also, you will need to type the date in... I don't know
whether the Before Update event of the date control will occur if the
value is being entered via a date picker control. If you are entering
the date into the date field into an existing record, all you are
achieving is editing the date in the existing record, which is
obviously not what you want. The alternative, though I can't see it
would be any easier, is to put an *unbound* textbox, maybe in the
header section of the subform, where you can type in the date, and use
some code to react to whether the date is already used. Once again,
if you are using a date picker control, your code might need to be on
the date picker's event property, rather than the date control on your
form.

Hope that might get you closer, and also help you to specify further
what you really want to happen.

- Steve Schapel, Microsoft Access MVP
 
Steve,

I removed the date picker so now it's just an unbound text box on my subform that the user enters the date. I tried your code to check for duplicate dates and I can't get it to work; I've also tried other suggestions such as:

Private Sub Date_AfterUpdate()
Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Str(Me!Date)
rst.FindFirst "Date =" & strSearchName
If rst.NoMatch Then
'MsgBox "Record not found"
Me.Recordset.AddNew
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close
End Sub

unfortunately I can't get this to work either - I had to substitue rst with the actual "Me.RecordsetClone" and when I tested it, it told me no records were found for that date however a record did exist. I am very frustrated. Is there anything obvious that I'm doing wrong?

Thanks for all your help.
 
Back
Top