UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cascading Dropdown, Access 2013    
 
   
dflak
post Nov 14 2017, 04:37 PM
Post#1


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


Moving this database forward one foot at a time. In the form frm_descent_edit, I specify a type aircraft and I want the next field to have a drop-down list limited to the types of jumps that can be done from that type aircraft. You can either do a very specific type of jump from an aircraft or you can do all types. So it's all or one. I'm not dealing with subsets. So if the aircraft is capable of all jumps I want to query every kind of jump other than all otherwise I want to query the specific kind of jump.

The type of jump gets its source from qry_desc_type. The code correctly rewrites the SQL for the query, but the correct dropdown doesn't appear. For C-130 it should show FF and Static. For Lynx it should just show Static.

CODE
Public Function GetTypeDescent(TypeAcft As Variant) As String
Dim TypeDesc As String
Dim TypeOp As String

'Dim rst As DAO.Recordset
Dim strSQL As String
Dim DescType As Variant

DescType = DLookup("lk_acft_type_desc", "tbl_lk_acft", "lk_acft_type = [Forms]![frm_descent_edit]![desc_acft]")

If DescType = "All" Then
    TypeOp = "<> " & Chr(34) & "All" & Chr(34)
Else
    TypeOp = "= " & Chr(34) & DescType & Chr(34)
End If

strSQL = "Select distinct lk_acft_type_desc from tbl_acft where lk_acft_type " & TypeOp

CurrentDb.QueryDefs("qry_desc_type").SQL = strSQL

End Function

Attached File(s)
Attached File  Unit_Training.zip ( 258K )Number of downloads: 8
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
RJD
post Nov 14 2017, 05:47 PM
Post#2


UtterAccess VIP
Posts: 7,848
Joined: 25-October 10
From: Gulf South USA


Hi: If I understand this correctly, I don't think you have to rewrite the SQL for the query to get what you want. I added a table of aircraft/descent combinations and used that in the form along with a requery when you step into a record (actually the first two fields).

See if this does what you want ... It's been a long time since I had jump gear on, but maybe I didn't fall on my third point of contact this time ...

(No C17 on the a/c list ...?)

HTH
Joe

(Edited to post slightly revised attachment.)
Attached File(s)
Attached File  Unit_Training_Rev1.zip ( 58.89K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dflak
post Nov 15 2017, 11:26 AM
Post#3


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


Thanks, this is the way I will go. I would like to give the end user more flexibility than "all or one." However, I would still like to find out how to use the other method. I've done it before, but I can't seem to find the database in which I did it.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
RJD
post Nov 15 2017, 11:30 AM
Post#4


UtterAccess VIP
Posts: 7,848
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Dan. Good luck with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
River59
post Nov 15 2017, 12:17 PM
Post#5



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


If you are using a drop down list, why not default to select all but allow the user to clear and select the jumps that they want. Then the query will include everything selected?
Am I missing something here?

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
dflak
post Nov 15 2017, 01:03 PM
Post#6


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


I need to show only those types of jumps that can be performed for the aircraft selected. I have 4 tables to support this effort:

- a lookup table for the types of aircraft
- a lookup table for the types of jumps
- a table that matches aircraft types with jumps - this may have multiple entries. For example C-130 can have three types of jumps, CH-47 can have two and others only one.
- a table linked to the personnel table in a many-to-one relationship that has a history of jumps by aircraft type, jump type and date accomplished.

So when logging a jump, I click a button on the personnel form that opens a data entry form for the jumps for the person. I select an aircraft type, and then I select a jump type and then fill in a date.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
River59
post Nov 15 2017, 01:19 PM
Post#7



Posts: 1,347
Joined: 7-April 10
From: Detroit, MI


QUOTE
I would like to give the end user more flexibility than "all or one."


I was responding to writing the query to answer the above. If there are 3 choices, do you want the user to be able to select 2 vs. all 3? To do that, you have to loop through the selected items.
I told you I was confused ... iconfused.gif

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
dflak
post Nov 15 2017, 01:34 PM
Post#8


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


The user may have multiple choices, but can only select one. For example, the from a C-130, a person can do a HALO Jump, Static Line Jump, Free Fall Jump. However, he can only do one of those type jumps at a time.

In my original design, I had it set up so if C-130 were paired with "All" then a person would get a list of all possible jumps. Otherwise, the person would get a list of only the one kind of jump that could be done from that aircraft. It subsequently occurred to me that there may be some aircraft from which several jump types can be performed, but not all jump types. So the "all or one" option doesn't work here. So, I am making the user enumerate the jump types for each aircraft.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
dflak
post Nov 16 2017, 11:49 AM
Post#9


Utter Access VIP
Posts: 6,017
Joined: 22-June 04
From: North Carolina


I found that bit of code that "installs" a new query. This is from a phone book program I wrote. I have two fields on which I can query. One is Business,Personal. The other is alphabetical: ABC, DEF, GHI, etc. - same is on a telephone keypad. The code is a little more complicated than it has to be as it decombines the ABC into Like A* or Like B* or Like C*.

CODE
Private Sub But_Search_Click()
Dim Alpha As String
Dim QryString As String
Dim WhereClause1 As String
Dim WhereClause2 As String
Dim qt As String
Dim k As Long

qt = Chr(34)
WhereClause2 = ""

QryString = "Select * from tbl_phones where "

' Personal or Business
If Me!combo_type = "(All)" Then
    WhereClause1 = "ph_rec_type like " & qt & "*" & qt
Else
    WhereClause1 = "ph_rec_type = " & qt & Me!combo_type & qt
End If

' Alpha select
If Me!combo_alpha = "(All)" Then
    WhereClause2 = "ph_fullname like " & qt & "*" & qt
Else
    For k = 1 To Len(Me!combo_alpha)
        WhereClause2 = WhereClause2 & "ph_fullname like " & qt & Mid(Me!combo_alpha, k, 1) & "*" & qt & " OR "
    Next
    WhereClause2 = Left(WhereClause2, Len(WhereClause2) - 3)
End If

QryString = QryString & " " & WhereClause1 & " AND (" & WhereClause2 & ") ORDER By ph_rec_type, ph_fullname, ph_title"

'MsgBox QryString

' This is the key part
CurrentDb.QueryDefs("qry_phones").SQL = QryString
Me.RecordSource = "qry_phones"
    

End Sub

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:22 PM