Form Based on Combo Box

[Matt]

Hi All,

I want to have a form filtered by the value in a combo box. I have a
table that has a Date, SSN, contract number, and 2 dollar amounts. I
want to select a SSN from the combo box, and then have the form show
all of the contract numbers along with the values. I also want to be
able to change these values also.

The caveat is that I only want to show the latest value, based on
date.


As always, I appreciate any help!

 

[Allen Browne]

You want to:
a) Select someone by SSN, and
b) Show only the most recent record by ContractDate.

The simplest solution will be to use the AfterUpdate event procedure of the
combo to assign a SQL statement to the form's RecordSource property. This
kind of thing:

Private Sub Combo1_AfterUpdate()
Dim strWhere AS String
Const strcHead = "SELECT TOP 1 [Table1].* FROM [Table1] WHERE ("
Const strcTail = ") ORDER BY [Table1].[ContractDate] DESC;"

If Me.Dirty Then Me.Dirty = False
If IsNull(Me.Combo1) Then
strWhere = "False"
Else
strWhere = "([SSN] = """ & Me.Combo1 & """"
End If

Me.RecordSource = strcHead & strWhere & strcTail
End Sub

If you want something else, or if the ContractDate is in another table, you
may need to use a subquery to get the most recent record for the person. If
that's a new concept, see:
http://allenbrowne.com/subquery-01.html

 

[Matt]

You want to:
a) Select someone by SSN, and
b) Show only the most recent record by ContractDate.

The simplest solution will be to use the AfterUpdate event procedure of the
combo to assign a SQL statement to the form's RecordSource property. This
kind of thing:

Private Sub Combo1_AfterUpdate()
Dim strWhere AS String
Const strcHead = "SELECT TOP 1 [Table1].* FROM [Table1] WHERE ("
Const strcTail = ") ORDER BY [Table1].[ContractDate] DESC;"

If Me.Dirty Then Me.Dirty = False
If IsNull(Me.Combo1) Then
strWhere = "False"
Else
strWhere = "([SSN] = """ & Me.Combo1 & """"
End If

Me.RecordSource = strcHead & strWhere & strcTail
End Sub

If you want something else, or if the ContractDate is in another table, you
may need to use a subquery to get the most recent record for the person. If
that's a new concept, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I want to have a form filtered by the value in a combo box. I have a
table that has a Date, SSN, contract number, and 2 dollar amounts. I
want to select a SSN from the combo box, and then have the form show
all of the contract numbers along with the values. I also want to be
able to change these values also.

The caveat is that I only want to show the latest value, based on
date.- Hide quoted text -

- Show quoted text -

Thanks Allen,

I am familiar with querys and sub querys but I struggle a little bit
on forms (most of our data is from an ODBC connection).

I have a question about your code, what exactly is Me.Dirty (I am
familiar with the ME concept, but what exactly is dirty)?

 

[Allen Browne]

Bound forms have a Dirty property, which indicates if an edit has begun that
has not yet been written to the table.

Before you can change the RecordSource of a form, any edits in progress must
be saved (or undone.) Access normally saves by default, but it is always a
good idea to do it explicitly. This can clear a queue of events, and avoid
weird errors. It can also avoid bugs like this one:
http://allenbrowne.com/bug-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You want to:
a) Select someone by SSN, and
b) Show only the most recent record by ContractDate.

The simplest solution will be to use the AfterUpdate event procedure of
the
combo to assign a SQL statement to the form's RecordSource property. This
kind of thing:

Private Sub Combo1_AfterUpdate()
Dim strWhere AS String
Const strcHead = "SELECT TOP 1 [Table1].* FROM [Table1] WHERE ("
Const strcTail = ") ORDER BY [Table1].[ContractDate] DESC;"

If Me.Dirty Then Me.Dirty = False
If IsNull(Me.Combo1) Then
strWhere = "False"
Else
strWhere = "([SSN] = """ & Me.Combo1 & """"
End If

Me.RecordSource = strcHead & strWhere & strcTail
End Sub

If you want something else, or if the ContractDate is in another table,
you
may need to use a subquery to get the most recent record for the person.
If
that's a new concept, see:
http://allenbrowne.com/subquery-01.html

I want to have a form filtered by the value in a combo box. I have a
table that has a Date, SSN, contract number, and 2 dollar amounts. I
want to select a SSN from the combo box, and then have the form show
all of the contract numbers along with the values. I also want to be
able to change these values also.

The caveat is that I only want to show the latest value, based on
date.- Hide quoted text -

- Show quoted text -

Thanks Allen,

I am familiar with querys and sub querys but I struggle a little bit
on forms (most of our data is from an ODBC connection).

I have a question about your code, what exactly is Me.Dirty (I am
familiar with the ME concept, but what exactly is dirty)?