Conditionally present a Drop down list (1 Viewer)

[dcavaiani]

If I have a form based on a table with 2 fields.

If field1 = "Customer", then I want field2 to show a dropdown list from another table with 1 field in it, else no dropdown list should apply.

Not quite sure best way to approach ?

 

[theDBguy]

Hi. Have you tried adding a criteria to your dropdown?

 

[dcavaiani]

Hi. Have you tried adding a criteria to your dropdown?

this was an excel data entry b4. i tried defining field2 at table level as a drop down but that gave little wiggle room when i tried to add the conditionality at form design

field1 field2

anything anything

Customer DropDown

repeats down form

 

[theDBguy]

this was an excel data entry b4. i tried defining field2 at table level as a drop down but that gave little wiggle room when i tried to add the conditionality at form design

field1 field2

anything anything

Customer DropDown

repeats down form

Hi. Rather than do it at the table, try doing it on a form.

 

[dcavaiani]

Hi. Rather than do it at the table, try doing it on a form.

That's where I kinda flamed out, trying to set a conditional format rule, which kinda worked cept cud not get to the 2nd table with the dropdown values in it.

Also tried setting a CONTROL SOURCE clause for field2 but did not quite "compute". I guess the issue is figuring how to - "referring to" either the 2nd table or to the sql which creates it (the 2nd table).

 

[theDBguy]

That's where I kinda flamed out, trying to set a conditional format rule, which kinda worked cept cud not get to the 2nd table with the dropdown values in it.

Also tried setting a CONTROL SOURCE clause for field2 but did not quite "compute". I guess the issue is figuring how to - "referring to" either the 2nd table or to the sql which creates it (the 2nd table).

Are you able to post a sample copy of your db? In order for us to tell how to fix what you've tried, we may have to see it.

 

[dcavaiani]

Here is the database

 

[theDBguy]

Here is the database

Hi. Thanks. The attached just does what you asked for. I am not sure if this is going to be useful for you though.

 

[dcavaiani]

I guess I do not see a lot of benefit from creating a form in Datasheet view - rather than just going directly to the table itself for data entry ???

 

[theDBguy]

I guess I do not see a lot of benefit from creating a form in Datasheet view - rather than just going directly to the table itself for data entry ???

Hi. Maybe you can't, but we always recommend to use forms instead of editing data directly from the table. For example, your original request can't be done at the table level, so I suggested to use a form instead.

 

[dcavaiani]

That example scared me away cause when I entered data in field2 it appeared the same in every row on the form

 

[theDBguy]

That example scared me away cause when I entered data in field2 it appeared the same in every row on the form

That's because you were using an unbound control. That's why I said, I didn't think the sample would be of any use to you. No matter what you pick in field2 will not be stored in the table, if that's what you were trying to do.

 

[dcavaiani]

Alas, I guess it is simply not possible to turn on or off a dropdown list in the same column of a form field ON A ROW BY ROW BASIS - based on the value of another field.

 

[JCColindres]

How about making it visible instead ?

If field1 = "Customer" Then
Me.field2.Visible = True
Else
Me.field2.Visible = False
End If

 

[theDBguy]

Alas, I guess it is simply not possible to turn on or off a dropdown list in the same column of a form field ON A ROW BY ROW BASIS - based on the value of another field.

Hi. Of course it is possible. In fact, I think there are at least three ways to do it. We just need more information about what you're trying to accomplish, so we can give some appropriate suggestions.

 

[dcavaiani]

This is what Excel is: .... used for spacing

Field1.............Field2........Field3......etc., etc.

Rich...............SAT
.....................08/06/19
.....................IN.............OUT
Customer........Conery-1
Am................700............800
Pm
Customer
Am
Pm
etc.
etc.

 

[isladogs]

For info, I've moved the posts by JCColindres to a new thread https://www.access-programmers.co.uk/forums/showthread.php?t=308217

 

[theDBguy]

This is what Excel is: .... used for spacing

Field1.............Field2........Field3......etc., etc.

Rich...............SAT
.....................08/06/19
.....................IN.............OUT
Customer........Conery-1
Am................700............800
Pm
Customer
Am
Pm
etc.
etc.

Actually, that is another problem. Access is not the same as Excel. We don't recommend storing the data into tables like that. In database design, there is a principle we follow called Normalization. If you're more comfortable using Excel, then maybe you need to use Excel and save Access for later. Just my 2 cents...

 

[dcavaiani]

Actually, that is another problem. Access is not the same as Excel. We don't recommend storing the data into tables like that. In database design, there is a principle we follow called Normalization. If you're more comfortable using Excel, then maybe you need to use Excel and save Access for later. Just my 2 cents...

I am fully aware of that, and that's how it works now (Excel is transferred over to Access Tables). However, I do see advantages to NOT have to have anything "to do" with the Excel side at all - So that's why the search for a "nearly" comparable solution - wholly within Access.

 

[theDBguy]

I am fully aware of that, and that's how it works now (Excel is transferred over to Access Tables). However, I do see advantages to NOT have to have anything "to do" with the Excel side at all - So that's why the search for a "nearly" comparable solution - wholly within Access.

Hi. If you are determined to move your process from Excel to Access, then you need to prepare yourself to do it the "Access way." Just transferring worksheets into tables may not cut it. You'll have to normalize the data. After you have designed a solid table structure, then you can design your forms.