Rowsource of Field List

[Guest]

Can anyone help me?

I have a combo box with a record sourse of Field List. I can then select one
of several date fields in another table.

However I cannot then use this field as I want.

I want an Unbound field to display the contents of the related field if that
makes sense!

Maybe an example would help:

Table 1: id, date1, date2, date3

Table 2: id, DateToUse (linked to field list of Table 1)

On a form I have

id _______ DateToUse __________

Date _________


The unbound field date should display the actual date in either Date 1, Date
2 or Date 3 depending on which is selected in DateToUse, without having to
use multiple if statements as there could be numerous dates in Table 1.

Is there any way of doing this?

 

[Larry Linson]

I have a combo box with a record
sourse of Field List. I can then select
one of several date fields in another table.

However I cannot then use this field as I want.

I want an Unbound field to display the
contents of the related field if that
makes sense!

No, it is not clear from your description what part, if any, the Combo Box
plays, nor what "the related field" is related _to_. Alas, an example
showing "id" which could be the "id" field of either of two tables isn't
much help either. Are these "id" fields the same number for related records
(e.g., a one-to-one relationship). Although the description isn't clear,
there are table layouts/designs that could allow selection of the
appropriate date field by joining. This arrangement, which is
"unnormalized", does not.

If you could clarify what you have with meaningful names rather than "date
1" or "date 2", and explain what you are trying to _accomplish_ rather than
the technique you think will accomplish it, perhaps someone could offer a
worthwhile suggestion.

Larry Linson
Microsoft Access MVP

 

[Guest]

OK, sorry if it wasn't clear enough. Let me try again.

This is what I currently have although what I want is frmOrderList with the
UnboundDate working so any alternative way of doing it would be good.

tblOrder OrderNumber, OrderStatusLnk, OrderDate, PaymentDate,
DespatchDate ...

tblOrderStatus StatusID, StatusName, LastDate

OrderStatusLnk is linked to StatusID

Example data in tblOrderStatus (note LastDate is a TEXT field with a lookup
to the field list of tblOrder):

1, "Order Started", "OrderDate"
2, "Payment Received", "PaymentDate"
3, "Stock Allocated", "PaymentDate"
4, "Order Sent", "DespatchDate"

frmOrderList

List all orders with relevant fields such as

OrderNumber StatusName UnboundDate

The Unbound Date should be one of the dates in the tblOrder. Which one,
depends on the status of the order. In other words, if the order has a status
of 2 (Payment Received), then the Unbound Date should display
tblOrderStatus.PaymentDate but if it is 4 (Order Sent), it should display
tblOrderStatus.DespatchDate.

I can achieve this with a load of if..else statements but if I had many more
dates, it would get very messy.

Mark

 

[Ken Snell [MVP]]

Larry has just gone a well deserved vacation, so let me see if I can jump
here for him.

I've read through the thread, and it appears that you want to allow the
choice of a "date type" selection from a combo box, and then to display a
"date" on the form?

You don't say what the RowSource for the combo box is, other than saying
it's a "field list" -- but of which table? What are the values of the "field
list"? What is the RecordSource of the form on which the combo box is
located? What is the relationship between tblOrder and tblOrderStatus (I
don't see any "obviously" named fields that would appear to be linking
fields)? What type of value is to be displayed in UnboundDate control? What
type of data are in your tables?

We need some more specific information here to try to help. Showing us
examples of the current data in the tables, and how those data would be
displayed based on a selection in the combo box, will help.

Also, not a good idea to use a "lookup field" in your table. That just makes
problems down the road when you "forget" what is actually being stored in
that field and think it's what's being displayed. See this article for more
info:
http://www.mvps.org/access/lookupfields.htm

 

[Guest]

I think what I am trying to do here may not be possible or too complicated to
explain on a message bord like this. I dont think I can explain it any easier
because the clarification you ask for is in my second posting.

You don't say what the RowSource for the combo box is, other than saying
it's a "field list" -- but of which table?

Sorry, I am not sure what sort of further clarification you want.

located? What is the relationship between tblOrder and tblOrderStatus (I
don't see any "obviously" named fields that would appear to be linking
fields)?

What type of value is to be displayed in UnboundDate control? What
type of data are in your tables?

The fields with date in the name are all date fields.

We need some more specific information here to try to help. Showing us
examples of the current data in the tables, and how those data would be
displayed based on a selection in the combo box, will help.

Actually, the more i read your questions, the more I think I must have
something fundametally wrong with my originall question because I thought I
had answered them all.

Oh well if it can't be done, it can't be done. I'll just use 7 or 8 nested
ifs. ( I have more dates in the actual table than I listed in the example.)

Sorry to confuse you and thanks for trying.

 

[Ken Snell [MVP]]

If all you're wanting to do is to display a text string in a control, based
on a value in another control, perhaps a DLookup function will serve your
purposes. It will allow you to retrieve a single value from a table based on
a criterion expression.

You could use an expression similar to this as the ControlSource of a
textbox (the following example assumes that the data type of MatchField is
numeric):

=DLookup("FieldToBeRetrieved", "TableName", "MatchField=" &
[NameOfControlOnForm])
--

Ken Snell
<MS ACCESS MVP>