Using combo box to look at history not working

  • Thread starter Pamela via AccessMonster.com
  • Start date
P

Pamela via AccessMonster.com

Hello again;

Got another one driving me nuts! Here is what I'm trying to do. I have a
table that keeps a history of Contact Dates. On my form, I have a combo box
which is resized so the it simply looks like a down arrow next to the history
date field. I thought this would be a neat way to allow the users to view
previous contact dates like they have asked me to allow. I have done this
before for a State lookup box, but can't seem to get this to work.

The combo box ([cmbDateHistory]) gets it's row source from the following
query:

SELECT tblContactDateHistory.ContactDate
FROM tblContactDateHistory
WHERE (((tblContactDateHistory.RecID)=[Forms]![F_Solicitation]![Num]));

I want the combo box to display only the dates for the specific record id,
and it works, until I move to a new record. It then displays the results
from the first record it was clicked on. I have tried requery on the on-
click event, but it still doesn't "requery" for the current record when it is
clicked. :

Private Sub cmbDateHistory_Click()
DoCmd.Requery ([cmbDateHistory])
End Sub

What am I doing wrong? Ugh!
 
S

Steve Schapel

Pamela,

Try it on the combobox's Enter event rather than Click, and try it like
this...
Me.cmbDateHistory.Requery
 
P

Pamela via AccessMonster.com

This worked. Thanks. I'm still learning VB... One cusiosoty though, the
date is inserted into the table twice...

Steve said:
Pamela,

Try it on the combobox's Enter event rather than Click, and try it like
this...
Me.cmbDateHistory.Requery
Hello again;
Click to expand...
[quoted text clipped - 23 lines]
What am I doing wrong? Ugh!
Click to expand...
Click to expand...
 
S

Steve Schapel

Pamela,

You didn't mention anything before about the date being entered into a
table. What table? When is the date entered into the table? Is this
combobox you have been referring to a bound control?... I had assumed
unbound. Can you give an example?
 
P

Pamela via AccessMonster.com

Hi!

On my form there is a control "Contact Date". The users wanted this control
to be able to keep a history of last contact dates & be able to view these
dates. Maybe there is a better way to do this, but on the after update event
for the textbox, I have the following code:

DoCmd.SetWarnings No 'turn off system messages

SQL1 = "INSERT INTO tblContactDateHistory (RecID, ContactDate) Values ("
& [Num] & "," & """" & [ContactDate] & """" & ")"

'DBEngine(0)(0).Execute SQL1, dbFailOnError
If Not IsNull([ContactDate]) Or [ContactDate] <> "" Then
DoCmd.RunSQL SQL1
End If
If Not IsDate([ContactDate]) And Not IsNull([ContactDate]) Then 'allow
Null date
MsgBox "Please Enter A Valid Date", vbOKOnly, "Invalid Date"
End If
If [ContactDate] > Date Then
MsgBox "Please Enter a Valid Date", vbOKOnly, "Future Date"
End If

The date is appended to the table " tblContactDateHistory ", only it does ot
twice.

Next to the textbox I have a combo box which is sized so only the arrow for
the control is visible (there is no visible "textbox". The testbox displays
the current contact date (from the Solicitations table), whereas the row
source for the combo box is a query on the Last Contact Dates table.

Everything seems to work as it should except for the date being entered twice
into the Last Contact Date table.
 
S

Steve Schapel

Pamela,

I am sorry, I can't see what the problem is here. I notice the line
'DBEngine(0)(0).Execute SQL1, dbFailOnError
is commented out. If you ran the code without this being commented out,
it would result in the date being added twice, since this does
essentially the saem thing as DoCmd.RunSQL SQL1. But otherwise I can't
see why you get the duplication.

There are a couple of peculiarities about the code. One is that you are
doing some validation of the ContactDate value entered *after* your code
to append the data to the table. So the effect of this will be that the
user will get a messagebox to tell them the date is not valid, but that
date will nevertheless still be used. The other is that you are
apparently treating the ContactDate as text rather than a date data
type... is that what you intended?
 
Top