List box subform question

[livin''''life]

I am in the Amry and am building a database to make my job a bit easier. I
have a table as follows where the primary key is both Unit and date. I may
have the same unit more than once but with different dates and I may have
multiple units with the same date. Previously I was using only the Unit as
the Primary key but I need to modify this because of the above situation.

Unit Date Arrived Date departed...etc
123AR 2009/09/01 info
123AR 2008/09/01 info
432AR 2009/09/01 info
432AR 2007/01/01 info

On my form, I would like to have a combo box where i can pick one of the
units and display their record. I have the combo box set up to display (in
one column) the unit and the date arrived but it does not seem to work
correctly. Do I need to set this up on a subform so I can link master and
child fields or is there a way to keep it on the form? Thank you for your
help.

 

[Dirk Goldgar]

I am in the Amry and am building a database to make my job a bit easier. I
have a table as follows where the primary key is both Unit and date. I
may
have the same unit more than once but with different dates and I may have
multiple units with the same date. Previously I was using only the Unit
as
the Primary key but I need to modify this because of the above situation.

Unit Date Arrived Date departed...etc
123AR 2009/09/01 info
123AR 2008/09/01 info
432AR 2009/09/01 info
432AR 2007/01/01 info

On my form, I would like to have a combo box where i can pick one of the
units and display their record.

Do you want to select the unit? Or the unit/date combination. If you want
to select just the unit and see all its records for the various dates, then
you really should use a main form/subform arrangement -- main form based on
the Units table, subform based on the table of UnitsArrivalDates (or
whatever this table is called).

If you don't have a table of Units, it's likely that you should -- certainly
you should if you have data that pertains to the unit no matter what its
arrival date is.

I have the combo box set up to display (in
one column) the unit and the date arrived but it does not seem to work
correctly. Do I need to set this up on a subform so I can link master and
child fields or is there a way to keep it on the form? Thank you for your
help.

If you don't have a main form based on Units, you could probably use
something like this for the rowsource of a combo box to select unit + date:

SELECT
Unit & "|" & Format([Date Arrived], "yyyymmdd")
As UnitDate,
Unit & " " & Format([Date Arrived], "yyyy/mm/dd")
As UnitDateDisplay,
Unit,
[Date Arrived]
FROM [YourTable]
ORDER BY Unit, [Date Arrived]

Then your combo box would have these properties:

Bound Column: 1
Column Count: 4
Column Widths: 0";1.25"; .5"; .75"

Note: the Column Widths I suggested are only a guess, except that the first
column should be hidden.

When you use the combo box to select and navigate to a particular record,
you can write this code:

'------ start of example ------
Private Sub cboUnitDate_AfterUpdate()

Dim strCriteria As String

With Me.cboUnitDate

If Not IsNull(.Value) Then

strCriteria = _
"Unit='" & .Column(2) & _
"' AND Date Arrived]=" & _
Format(.Column(3), "\#mm\/dd\/yyyy\#")

Me.Recordset.FindFirst strCriteria

End If

End With

End Sub
'------ end of example ------

That's "air code", and I've only guessed at various names, but it should
give you the idea.

However, I still think you probably ought to be using a main form/subform
arrangement.