Form not cooperating

[evega6686]

Sorry, I'm new to this, including the Forum. This is actually the first time I have used a Forum for help.

I was hoping the Requery would populate my List Box with CID that had a matching CType/PGCode/PDCode combination for a filter. Nothing appears when I switch from Combo to Combo. I hesitate removing ALL the code from AfterUpdate Event since the CType is actually coming from two seperate tables
FormLoad() has CCode.CCode
then further down..
AfterUpdate() has CCAH.Year_Month

 

[vbaInet]

Steady vbaInet. That's a lot of bold text and bright colors, and yeah, I know, I used ALL CAPS in my post too, but . . .

Don't worry I'm not shouting, I'm only highlighting for clarity. The colours go in sync, just like you would in a cascading combo box

 

[evega6686]

I agree, progress is being made and I am hopeful (determined) to get this to work. I was not offended in any way by the Color/Bold of the font. trust me, I am frustrated too (lol).

Thanks for the encouragement.

I sent a screen shot of the DesignView of the form, in the chance that may help in my explaination.

 

[vbaInet]

Where does the listbox come in? I thought we were dealing with combo boxes?

You'll get there, just focus.

 

[evega6686]

The list box is where I want the result to be displayed.

There is a main table (MasterCID) that uses the form [CID Gen] to add a record to the table. Prior to adding the record to the table the end user will filter CID in a List Box (LastCID) to show which CID's have been used already. Each of the drop down combo boxes uses a portion of the CID to filter
Combo1 (CType) filter MasterCID.CType
Combo2 (PGCode) filter MasterCID.PGID (where PGCodes.PGCode is the text, and PGCode.ID is the auto-number, PK)
Combo3 (PDCode) filter MasterCID.PDID (where PDCodes.PDCode is the text, and PDCode.ID is the auto-number, PK)

The end user is to select from the drop-down (Combo boxes) to enforce business rules.

Breaking it down into smaller chunks:
I have made a seperate ListBox (not shown on the screen print I attached earlier) that uses the following SQL to pull from the form, which works without me having to go into DesignView to activate it. It doesn't give me the CID I want, If I try to attach CID to my QBE then no results are returned. SO, if I could use this as a filter for my original List Box that I have on the form it would work for me (I would just hide the second list box so the end users wouldn't know)

SELECT MasterCID.CType, PGCodes.ID, PGCodes.PGCode, PDCodes.ID, PDCodes.PDCodes
FROM PDCodes INNER JOIN (PGCodes INNER JOIN MasterCID ON PGCodes.ID = MasterCID.PGCodeID) ON PDCodes.ID = MasterCID.PDCodeID
WHERE (((MasterCID.CType) Like [Forms]![CID Gen]![CType]) AND ((PGCodes.ID) Like [Forms]![CID Gen]![PGCodeID]) AND ((PDCodes.ID) Like [Forms]![CID Gen]![PDCodeID]));

 

[vbaInet]

I still don't completely follow. Can you give the hierarchy of your combo boxes and listbox.

Does the user select an item from the listbox, which then filters Combo1, and Combo1 filters Combo2 etc?

Or

Does the user select an item from Combo1, which filters Combo2 etc, which then filters the listbox?

Let's see the SQL of the three combo boxes and the listbox and also a screenshot of your form.

 

[evega6686]

"I still don't completely follow. Can you give the hierarchy of your combo boxes and listbox.
Does the user select an item from the listbox, which then filters Combo1, and Combo1 filters Combo2 etc?
Or
Does the user select an item from Combo1, which filters Combo2 etc, which then filters the listbox?
Let's see the SQL of the three combo boxes and the listbox and also a screenshot of your form"

The user is to select one of the Option buttons to set the CType. They select from the drop-down combo boxes; the CType, PGCode and PDCode are used to identify the CID. The CID's that have the CType/PGCode/PDCode that matches should be displayed in the "Last # In Series" List box (just for viewing)

an RID and "Enter Next # In series" is manually added by the user and they hit "ENTER" to save the record.

Also, right now, after they save the record, how would you set it up so that they can start the process over again (Refresh All)?

ENTER Button SQL(Event procedure):
rivate Sub SaveRecord_Click()
On Error GoTo Err_SaveRecord_Click
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "UpdateMasterCID"
DoCmd.OpenQuery "AppendMasterCID"
DoCmd.SetWarnings False
DoCmd.OpenQuery "DeleteQ"
DoCmd.Close acQuery, "UpdateMasterCID"
DoCmd.SetWarnings True

Exit_SaveRecord_Click:
Exit Sub
Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click

End Sub


Best,
evega6686

 

[vbaInet]

Right, so it's a search form.

What I wanted to know was this hierarchy:
Option button > Combo 1 > Combo 2 > Combo 3 > Listbox

I'm a bit disappointed that despite my instructions, you still left all the other redundant code like Me.Combo.Rowsource in there.

Now, because you have many dependencies, you need to think about what happens when the root (i.e. the option button) is selected. So here are your options:

1. It requeries Combo1,2,3 and the listbox. Unnecessary but doable.
2. It requeries Combo1 and sets the RowSource of Combo2,3 and listbox to nothing. A better option because the database doesn't have to work.

Which option do you prefer?

 

[evega6686]

Option one sounds best.

Thanks

 

[vbaInet]

Ok. Let's start with the query. Could you post the query for each control in this format:

Option button SQL:
SELECT... FROM... etc

Combo1:
SELECT... FROM... etc

Combo2:
SELECT... FROM... etc

Combo3:
SELECT... FROM... etc

Listbox:
SELECT... FROM... etc

Put in a text file in the exact format as above.

 

[evega6686]

I have attached the text file as requested. I can't thank you enough for helping me out.

 

[vbaInet]

I'm guessing Combo142 (you need to work on your naming convention) refers to Combo1?
Why does its Row Source differ in the Load event of the form and After Update event of the combo box?

Attach some sample data for each of the queries linked to the combo boxes and listbox... same format as before and 3 or so non-confidential records per query will suffice.

 

[evega6686]

CID table:
ID (AutoNumber, PK)
CID (UNIQUE)
RID
Frequency
CType (CCode)
PGCode
PGCodeID
PDCode
PDCodeID
CIDNo
CreateDate (=Date())
EmployeeID
EmployeeName

Combo142 can be one of two tables Auto and CCAH, which are the CType

ID CID RID Frequency Type CType PGCode PDCodeID PDCode ProductCodeID CNo CreateDate EmployeeID EmployeeName
21 12345678912 3280 3400 Non Auto 1006 EV 02 ABC 01 5/1/2015 15 [FName + LName]

Option button SQL:
Switches tables used in CType

Combo1:
Can be either 4-digit text, or 4-digit number; stored on 2 seperate tables (which is the reason for the switch based off the Option Buttons 121 & 123)

Combo2:
Drop down list allows you to choose the Code and Desc (description); hidden column in the field is the ID (table joins to MasterCID based off the ID, other two columns are the Code and Desc)

Combo3:
Drop down list allows you to choose the Code and Desc (description); hidden column in the field is the ID (table joins to MasterCID based off the ID, other two columns are the Code and Desc)

Listbox:
Should only show CID's that have the same CType/PGCode/PDCode combo, the NextNoInSeries, as well as the RID, are different for each CID.

I hope this is what you are requesting.

 

[evega6686]

Combo142 is not what it is actually called. I de-identified my fields at the request of my manager.

 

[burrina]

I would suggest a Select Union Statement that uses a query.
Example: Of course modify to your needs.

cbogroup 'combobox
SELECT [egroup], [egroup] FROM qryEmailClientsByGroup UNION SELECT "*","<< ALL >>" FROM qryEmailClientsByGroup;

Private Sub cbogroup_AfterUpdate()      'Refresh Data.
Me.lstlist.Requery
End Sub

HTH

 

[evega6686]

burrina:

I'm not sure I follow...Which Combo box should I attach this code?

 

[vbaInet]

burrina:

I'm not sure I follow...Which Combo box should I attach this code?

That's just aesthetics. It can be addressed at a much later stage.

What I requested was data, i.e. records of each of the 4 queries bound to the combo boxes. So, I mean sample records from CCode, PGCode, PDCodes and LastNoInSeries.

 

[evega6686]

CType (also part of the event procedure:
SELECT [CCode].[CCode] FROM CCode ORDER BY CCode;
CCode CType:
SampleData=
ABCB
BLOM
CLOM
DPOM
YYMM
_______________________________________
CCAH CType:
SampleData=
1505
1504
1503
1502
1501
1412
etc.
_________________________________
PGCode Combo Box:
SELECT ID, [PGCode] & " " & [PGCodeDesc] AS Expr1 FROM PGCodes ORDER BY PGCodes.PGCode;
PGCodes:
SampleData=
Column Headers= ID|Expr1 (PGCode and PGCodeDesc Concatenated)
1 AT AllTime
2 CL ConLate
3 CV ConValue
4 IT InTime
5 LM LastMeasure
6 PE Permission
etc.
___________________________________
PDCode Combo Box:
SELECT PDCodes.ID, [PDCodes] & " " & [PDCodeDesc] AS Expr1 FROM PDCodes ORDER BY PDCodes.PDCodes;
PDCodes:
SampleData=
Column Headers= ID|Expr1 (PDCode and PDCodeDesc Concatenated)
1 AFG AllFGroup
2 BCC BestCC
3 BLN NotValid
4 INS (Desc)
5 MLM (Desc)
etc.
_____________________________________
Last # In Series (This is the one I am having problems with. Everything else is working properly):
SELECT LastNoInSeries.[CID] FROM LastNoInSeries;
I used two queries to get to the LastNoInSeries: 1-LastInSeries, 2-LastNoInSeries:
_____________________________________
LastInSeries SQL Statement:
SELECT MasterCID.CType, PGCodes.PGCode, PDCodes.PDCodes
FROM PDCodes INNER JOIN (PGCodes INNER JOIN MasterCID ON PGCodes.ID = MasterCID.PGCodeID) ON PDCodes.ID = MasterCID.PDCodeID
WHERE (((MasterCID.CType) Like [Forms]![CID Gen]![CType]) AND ((PGCodes.ID) Like [Forms]![CID Gen]![PGCodeID]) AND ((PDCodes.ID) Like [Forms]![CID Gen]![PDCodeID]));
LastInSeries:
SampleData=
CType PGCode PDCodes
BLOM CL BCC
LastNoInSeries:
SELECT DISTINCT MasterCID.[CID]
FROM MasterCID INNER JOIN LastInSeries ON (MasterCID.PDCode = LastInSeries.PDCodes) AND (MasterCreativeID.PGCode = LastInSeries.PGCode) AND (MasterCID.CType = LastInSeries.CType)
ORDER BY MasterCID.[CID];
LastNoInSeries:
SampleData=
Would give me the CID that has a matching CType/PGCode/PDCode Combo
22327654354
22327654355
22327654377
22327654379
22327654392

 

[evega6686]

Would DLOOKUP work for me?

 

[vbaInet]

No it wouldn't. I've just not had the time to look at your post yet, I will get back to you.