Max Date problem

[Guest]

Hello,

I have a subform with a combo box. The data for the combo box is limited to
show only the record with the most recent date - and when I go to the query
data, it does show only the most recent record - yet whenever I select that
entry, it pulls up the older data.
Why is that, and how can I get around it?

Thank you.

 

[Guest]

With "query data" I mean the row source list.

 

[Guest]

I guess I should say that I have conflicting goals here - I use the same form
for adding new records and editing existing ones. When I create a new record,
I need the combo box to show the record with the most recent date, but when I
edit records, I need the date shown that as entered, not the most recent one.

The only way that I see around this dilemma is to have separate forms for
adding and editing with separate queries to populate that box. It would be
nicer though to use the same form.
Can it be done, and if so, how?

 

[SusanV]

You can do this with a single form with a bit of VBA in the On Current Event
of the form, something along the lines of:

Private Sub Form_Current()

If Me.NewRecord Then
Me.YourComboBox.Rowsource = "SELECT something from Somewhere WHERE
....."
Else: Me.YourComboBox.Rowsource = "SELECT something from Somewhere"
End If

End Sub

Note that you have quite a bit of editing to do in the above aircode

;-)

 

[Guest]

Oh, that sounds interesting, thank you!
I'll get to work at it right away.

 

[Guest]

By the way, when I use your method, should I leave the Row source property
field free?

 

[SusanV]

It doesn't matter - it will be overwritten by the code anyways. I use this
method quite frequently for certain fields to be visible for existing
records and hidden for new records.

 

[Guest]

That comma from the SPNames expression is creating problems, the VBA editor
is expecting an end of statement when it reaches that point.

"SELECT tblDisclosures.DisclosureID, [tblSpeakers]![SPNameLast] & ", " &
[tblSpeakers]![SpNameFirst] AS SPNames FROM tblSpeakers RIGHT JOIN
tblDisclosures ON tblSpeakers.SpeakerID = tblDisclosures.SpeakerID;

 

[SusanV]

Try this - again, only partially done:

Private Sub Form_Current()

Dim strSQL As String

If Me.NewRecord Then
strSQL = "SELECT tblDisclosures.DisclosureID,
tblSpeakers.SPNameLast, " _
& "tblSpeakers.SpNameFirst AS SPNames " _
& "FROM tblSpeakers RIGHT JOIN tblDisclosures " _
& "ON tblSpeakers.SpeakerID = tblDisclosures.SpeakerID;"

Else
strSQL = "SELECT something from Somewhere"
End If
Me.YourComboBox.Rowsource = strSQL

End Sub

You might want to use a stored query instead of SQL, up to you really.

 

[Guest]

That didn't work, either, the commas are still a problem.
I like to work with SQL statements, even though I don't know much about SQL.
Usually it makes things easier.

 

[SusanV]

Often when I run into this sort of thing I'll use the query design view to
get the effect I need, then switch to the SQL view to get the SQL and paste
it where I need it - much quicker than debugging a missing parentheses or
somesuch...

 

[Guest]

In this case, I had to replace the " around the comma with ', and that did
the trick [as somebody else pointed out to me].
Now it's working!

Thanks a lot for your help!

One more question though, if I may - how do you hide things with this
method? I think this is a very neat technique, and I'd like to use it more in
the future.

 

[SusanV]

if me.newrecord then
me.textbox.visible = false
me.label.visible = false
else
me.textbox.visible = true
me.label.visible = true
end if

Keeps people from entering data into a field before the record is saved - in
my case I have a "save" button that adds a corresponding record to a
related table, and that field is in that table - so the form would error if
the user tried to put data in prior to the save...

Glad to help any time - believe me, I get more help than I give around here,
even without posting a question!

 

[Guest]

Ok, that seems to be pretty straightforward.
Thanks again.