Changing rowsource of combo based off selection of another combo

[d9pierce1]

HI all,
Does anyone have a good example of how to change the row source of combo box based on selection of another combo box?
Thank you,
Dave

[Bob Fitz]

Have a search for cascading combos. There must thousands of posts on that subject.

[Bob Fitz]

Here's a simple example. Post back with any questions.

[d9pierce1]

Hi Bob,
Thanks for the sample.
I think I can figure out at this point but what I was seeking was to actually use one combo and change its rowsource pending on what was selected in the first combo...

If Combo1 = 1;"Receive Funds"
Then combo 2 RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=28 OR [AccountTypeID]=29 ORDER BY [AccountDescription]; 
If Combo 1  = 2;"Deposit Funds"
Then combo 2 RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=31 ORDER BY [AccountDescription];
And so on....

I will assume I will have to write a Case Select for this and possibly also put that in OnCurrent also???
Dave

[Micron]

what I was seeking was to actually use one combo and change its rowsource pending on what was selected in the first combo

That is exactly what cascading combos do.

[Bob Fitz]

Hi Bob,
Thanks for the sample.
I think I can figure out at this point but what I was seeking was to actually use one combo and change its rowsource pending on what was selected in the first combo...

If Combo1 = 1;"Receive Funds"
Then combo 2 RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=28 OR [AccountTypeID]=29 ORDER BY [AccountDescription]; 
If Combo 1  = 2;"Deposit Funds"
Then combo 2 RowSource = SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo WHERE [AccountTypeID]=31 ORDER BY [AccountDescription];
And so on....

I will assume I will have to write a Case Select for this and possibly also put that in OnCurrent also???
Dave

Can you post a copy of your db

[Micron]

My apologies; I didn't review your 2 sql statements close enough. I agree that a select case would be one way (and easy to expand if necessary) but if there will only be 2 options in the combo, an IF block would suffice. I'd pass whatever sql portions are identical to string variables if they're long enough to be bothered doing that. I'll edit my post with an example shortly.

EDIT
This is just an example of one way. I think this would be the proper event:

Private Sub Combo1_AfterUpdate()
Dim strStart As String, strMid As String, strSort As String

strStart = "SELECT [qryAccountInfo].[AccountID], [qryAccountInfo].[AccountDescription], [qryAccountInfo].[AccountTypeID] FROM qryAccountInfo"

Select Case Me.Combo1
    Case 1
        strMid = " WHERE [AccountTypeID]=28 OR [AccountTypeID]=29"
        strSort = " ORDER BY [AccountDescription]" 'if this is always the same, put it once before Select Case
    Case 2
        strMid = " WHERE [AccountTypeID]=31"
        strSort = " ORDER BY [AccountDescription]"
    Case 3 
        ' and so on
End Select

End Sub

[d9pierce1]

Thanks Micron and all,
I appriciate the help of everyone and I do have 8 different options for combo 1 so I do think case select is the way to go,
Just was unclear with changing row sources as have never done that before
Dave

[d9pierce1]

Hi all,
Is there a way to create a incremental number in the table of access in a specified field? I have spent most of the day reviewing the post and such but cant seem to find what I am seeking?
May not be possible in the table? I have tried many things but cant get it to perform!

I have a append query that updates a table however it does not have the TransCode in it which is a unique number I get from a function and I need that number.
On form it works like clock work but this event does not open a form, when I click a button it fires the append query and updates a table with mismatched dates.

tblTransactions
TransactionID - AutoNumber
TransactionTypeID - Number
TransCode - Number (Which I want to make an incrementNumber like the TransactionID)
Plus more fields....

I use this on the forms to get the TransCode

Option Compare Database
Option Explicit


'This will return the last autonumber inserted into the table. It will be unique.
'You can format it into something else for display purposes.


Public Function fnHeaderID() As Long
    
    Dim sSql As String
    Dim db As Database
    
    sSql = "INSERT INTO tblUniqueIDs (UniqueDate) Values (Now())"
    Set db = CurrentDb
    
    db.Execute sSql, dbSeeChanges
    fnHeaderID = db.OpenRecordset("SELECT @@IDENTITY")(0)
    Set db = Nothing
End Function

But without a form open, I cant seem to get this number into table? I would actually like to just call that function at table level if possible?
Else, I need to find a way to create it. I tried putting fnHeaderID() in a query but then it changes all of them to the next number in sequence.

Any Ideas would be helpful, I have searched this forum, googled it, and cant find anything except how to change the primary key format and I need the PK besides this code.
Thanks
Dave

[d9pierce1]

Not sure what happened here but I created a new post and this last post of mine ended up in this post?
Weird

[d9pierce1]

Thank you Micron, that is huge help!
Dave