How to say if a cbo is empty or null? (Or what?) (1 Viewer)

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
OK, well you were correct when you said in an earlier post that probably do not understand Greek. However, if you can post your db in A2003 mdb format I may still be able to debug the code and correct where we are going wrong. I would at least like to try.

Can't save to mdb, got a warning message about some restrictions. Also, database is over 2mb (it's 4).

Can I upload it somewhere else and send the link (given that you can read accdb files?)
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
Can't save to mdb, got a warning message about some restrictions. Also, database is over 2mb (it's 4).

Can I upload it somewhere else and send the link (given that you can read accdb files?)
I use A2003 so I am unable to work with accdb. Try this:
Make a copy of the db with just the two forms that we have been discussing and any tables and queries that are used in/on those forms. Then try to convert to mdb. To reduce the size try Compact and Repair and then create a zip file to attach.
Past my bed time, so I'm off to the land of dreams. Will look at this again tomorrow. Good Luck!
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
I use A2003 so I am unable to work with accdb. Try this:
Make a copy of the db with just the two forms that we have been discussing and any tables and queries that are used in/on those forms. To reduce the size try Compact and Repair and then create a zip file to attach.
Past my bed time, so I'm off to the land of dreams. Will look at this again tomorrow. Good Luck!

Same applies here, as well!

Thanks a lot for all this help, I'll try to save to mdb and report back.
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
I cannot save in an earlier version because db uses features that require the current file format.

Problem is that I've deleted almost everything. Can't imagine what's left that prohibits the conversion, really, no fancy things used, in the first place.
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
I cannot save in an earlier version because db uses features that require the current file format.

Problem is that I've deleted almost everything. Can't imagine what's left that prohibits the conversion, really, no fancy things used, in the first place.
OK. One last suggestion.
Open form1 and make sure CarID has a value.
Open a new query in SQL view and paste in:
Code:
SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=" & Forms!Form1.CarID & " ORDER BY tblCarManuals.ManualNo;"
View the result and then save the query with a name of your choice.
In the On Open event code that we had, use the name of the new query as the domain in the DCount() function.
Let us know if you have any problems or don't understand.
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
OK. One last suggestion.
Open form1 and make sure CarID has a value.
Open a new query in SQL view and paste in:
Code:
SELECT tblCarManuals.ManualNo, tblCarManuals.CarID FROM tblCarManuals  WHERE 
(((tblCarManuals.ManualNo) Is Not Null)) AND tblCarManuals.CarID=" & Forms!Form1.CarID & " ORDER BY tblCarManuals.ManualNo;"
View the result and then save the query with a name of your choice.
In the On Open event code that we had, use the name of the new query as the domain in the DCount() function.
Let us know if you have any problems or don't understand.

Well, well, well... And the Oscar goes to..............

Before the nomination(!!), let me tell you what I did:

Your idea of assigning the result of the SQL statement to a query was excellent.

BUT, the statement you provided that referred to the loaded form (Forms!frm1, etc...), returned a syntax error. So I used the initial SQL as it was, since the query would run after form load, meaning, at a point where CarID would have already been assigned with a value (=qryAllBase.CarID).

And this thought was proved correct and of course the Oscar goes to you (let me share the glamour of handing it to you!).

Thaaaaaaaaaaaaaanks!!!
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
Well, I like to kid myself that I know a little about using Access but I have no experience at all in writing acceptance speeches. I believe it is customary to waffle on, endlessly thanking everyone who has every featured in ones life from ones parents to "Bertie" the gold fish that you keep in the living room that give you such wonderful and enlightening vision. I will just say that I think you sound like a happy person now of which I am glad and thank you for your kind words. And for the award, of course, I shall display it proudly in the living room. Right next to "Bertie" the gold fish. :D
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
Well, I like to kid myself that I know a little about using Access but I have no experience at all in writing acceptance speeches. I believe it is customary to waffle on, endlessly thanking everyone who has every featured in ones life from ones parents to "Bertie" the gold fish that you keep in the living room that give you such wonderful and enlightening vision. I will just say that I think you sound like a happy person now of which I am glad and thank you for your kind words. And for the award, of course, I shall display it proudly in the living room. Right next to "Bertie" the gold fish. :D

Feelings when programming can vary, I'm afraid, with the speed of light. If you don't want to follow any more, I'll understand (it's like being nominated with the wrong Oscar....;-). Also, I'm dropping the "Cars" simplification naming and using real case names.

Anyway, it seems the cbo does not behave. I understand it never did.

Cbo gets its values, as we've seen, via this code:

Code:
SELECT tblDocEgk.DocEgkID, tblDocEgk.TypeID FROM tblDocEgk WHERE (((tblDocEgk.DocEgkID) Is Not Null)) 
AND tblDocEgk.TypeID=qryAllBase.TypeID ORDER BY tblDocEgk.DocEgkID;

Okay. When main form opens (frmtblType (previously called "form1"), cbo is populated accordingly (based on TypeID values (previously called "CarID").

So now I have a combo box with say, one value. By using this code in AfterUpdate:

Code:
strRS = "SELECT qryDocEgk.Aregk, qryDocEgk.DocEgkID, qryDocEgk.number, qryDocEgk.Keimeno, qryDocEgk.selida FROM qryDocEgk "
 
  
  If Not IsNull(Me.TypeID) Then
    strRS = strRS & " WHERE TypeID = " & Me.TypeID
  End If
    strRS = strRS & " ORDER BY qryDocEgk.DocEgkID;"
   Me.lstAnalysis.RowSource = strRS
    Me.lstAnalysis.Requery

I used to project the relevant record to a listbox.

Problems started when I added more values to tblDocEgk, related to specific TypeID. Now, the cbo is still populated correctly (say, "carmanual1", "carmanual2", "carmanual3") but when I select any of the values, AfterUpdate action shows ALL records from tblDocEgk related to the preloaded TypeID (remember, TypeID is loaded from the very beginning).

What I need now, I guess, is a way to say:

When I click a value in the combo box, then load in the listbox the records related to the selected value, AND ONLY THIS, even if there are more records that are related with the TypeID.

Example (it seems I can't avoid "cars" cases):

Main form loads with all MERCEDES information. This form has the cboDocEgk combo in it. This cbo gets values from tblDocEgk. Values are: "Mercedes radio manual", "Mercedes navigation manual", "Mercedes Engine Manual", etc. These are my dropdown values.

When I select, say, "Mercedes Radio Manual", through an AfterUpdate action I want this record (and only this, even if there are more records related to Mercedes manuals) projected in the lstAnalysis listbox.

Embarrassed a bit, but giving it a try, anyway...
:eek:

edit for more explanation:

tblDocEgk is structured like this:

DocEgkID TypeID Text
Mercedes Radio Manual 1 Great Radio!
Mercedes Navigation Manual 1 Navigation made easy!
Mercedes Engine Manual 1 This engine roars!
Audi Radio Manual 2 This engine whispers.


From cbo, when I select "Mercedes Radio Manual", I want in the listbox the information "Great Radio!", displayed, and only that.......
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
Feelings when programming can vary, I'm afraid, with the speed of light. If you don't want to follow any more, I'll understand (it's like being nominated with the wrong Oscar....;-). Also, I'm dropping the "Cars" simplification naming and using real case names.

Anyway, it seems the cbo does not behave. I understand it never did.
Have no desire to withdraw from following this thread. Who knows, I could end up with another award to display on the other side of "Bertie". That would look nice and balanced.;)

Interested to see what you say about the cbo not behaving and that it never did. I have to say that I was rather surprised when you posted to say that you had found the solution by changing the criteria in the cbo's SQL. I could be wrong, but I can't see how you can reference the forms record source in that way. I still feel that the problem and solution lay with that criteria and that it needs to reference a control on the first form that holds the value that is to be used in that criteria.

What version of Access are you using. Only asking because I find it so frustrating sometimes when I am unable to examine someones db because I only have A2003 that I am considering buying a later version.
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
Have no desire to withdraw from following this thread. Who knows, I could end up with another award to display on the other side of "Bertie". That would look nice and balanced.;)

Interested to see what you say about the cbo not behaving and that it never did. I have to say that I was rather surprised when you posted to say that you had found the solution by changing the criteria in the cbo's SQL. I could be wrong, but I can't see how you can reference the forms record source in that way. I still feel that the problem and solution lay with that criteria and that it needs to reference a control on the first form that holds the value that is to be used in that criteria.

What version of Access are you using. Only asking because I find it so frustrating sometimes when I am unable to examine someones db because I only have A2003 that I am considering buying a later version.

Thanks Bob. Well, I returned back to previous saves, before our conversation here, and still, the problem was there.

Actually, I never changed the criteria in the cbo's SQL. I chose to stick to mine instead of referring to Forms!.... because, if you remember, the record source is already there (qryAllBase.TypeID) when form loads.

When form loads, its TypeID loads as well so why to refer to Forms!.. value? Anyway, I'm pretty sure this is not the problem now. Coloring and referencing work fine.

Problem is that when I send records to lstAnalysis listbox, ALL TypeID related records load, instead of only those that equal to the selected value from the dropdown list of the cbo.

I'm working hard for hours now to properly syntax and reference an AND condition saying "Load those and ONLY those values to the listbox which are related to the preloaded TypeID AND AT THE SAME TIME equal to the selected drop-dead (...sorry, down), value).......

No luck of course.
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
So, as I understand it now, you have a list box and you want its display to be governed by the selection made in the combo box.
If that is correct, please post the Row Source property of the list box, the Row source property of the cbo and the Bound Column property of the cbo.
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
So, as I understand it now, you have a list box and you want its display to be governed by the selection made in the combo box.
If that is correct, please post the Row Source property of the list box, the Row source property of the cbo and the Bound Column property of the cbo.

It is correct.

Listbox RowSource property:
Nothing (will explain in a moment)

Cbo RowSource property:
Code:
SELECT tblDocEgk.Aregk, tblDocEgk.TypeID FROM tblDocEgk 
WHERE (((tblDocEgk.Aregk) Is Not Null)) AND tblDocEgk.TypeID=qryAllBase.TypeID ORDER BY tblDocEgk.Aregk;

Cbo bound column:
=1

Cbo AfterUpdate Event:
Code:
Private Sub cboEgk_AfterUpdate()
' Filter the listbox based on the selected value in the cboEgk.
    FilterTypeListDocEgk
  Me.lstAnalysisTitle = "Information on " & cboEgk
  Me.tboColorWeb.BackColor = 2474495
    
End Sub


Private Sub FilterTypeListDocEgk()
   Dim strRS As String
   
    ' Filter the list box appropriately based on the combo box selection(s)
  strRS = "SELECT qryDocEgk.Aregk, qryDocEgk.DocEgkID, qryDocEgk.number, qryDocEgk.Keimeno, qryDocEgk.selida FROM qryDocEgk "
 
  
  If Not IsNull(Me.TypeID) Then
    [COLOR="Red"]strRS = strRS & " WHERE TypeID = " & Me.TypeID[/COLOR]
  End If
    strRS = strRS & " ORDER BY qryDocEgk.DocEgkID;"
   Me.lstAnalysis.RowSource = strRS
    Me.lstAnalysis.Requery

End Sub

This how (above) listbox lstAnalysis is populated. Now, somewhere near the strRS variable, I guess I must add a criterion saying:

strRS = strRS + the condition of comparing preloaded TypeID with the one found in the qryDocEgk + the condition of getting as value only the record that equals to the value selected in the cboEgk.
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
This how (above) listbox lstAnalysis is populated. Now, somewhere near the strRS variable, I guess I must add a criterion saying:

strRS = strRS + the condition of comparing preloaded TypeID with the one found in the qryDocEgk + the condition of getting as value only the record that equals to the value selected in the cboEgk.
That sounds right. Couple more questions:

1 Do you have a control on the form called TypeID

2 Is the value of the field called "Aregk" that is in both Row source properties the same and could we use this in the criteria.
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
That sounds right. Couple more questions:

1 Do you have a control on the form called TypeID

2 Is the value of the field called "Aregk" that is in both Row source properties the same and could we use this in the criteria.

1. Yes, it's a hidden textbox. Form's Record Source is qryAllBase. TypeID is part of this query.

2. Yes, it's the same. I don't know though if this makes things worse, but a TypeID may have many "Aregks" and one "Aregk" may refer to many TypeIDs.

It's a Books & Authors analogy....

(sorry)...
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
I built a query from tblDocEgk.

Criterion for TypeID: [Forms]![frmtblType]![TypeID] (TypeID is there, remember?)
Criterion for "Aregk": [Forms]![frmtblType]![cboEgk] (cboEgk value is also there, just selected)

Then, on AfterUpdate event of the cboEgk, I just wrote:

Me.lstAnalysis.RowSource = "query".

Et voila........ still trying to believe.... trying to reproduce errors, no errors.

Could it be THAT simple????? After 12 hours of dissecting my brain...........
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
1. Yes, it's a hidden textbox. Form's Record Source is qryAllBase. TypeID is part of this query.

2. Yes, it's the same. I don't know though if this makes things worse, but a TypeID may have many "Aregks" and one "Aregk" may refer to many TypeIDs.

It's a Books & Authors analogy....

(sorry)...
So, if we limit the display in the list box to only records which have the same "Aregk" as the "Aregk" in the cbo, would that give us the dedired result.
 

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
Sorry, I seem to have missed your last couple of posts. Suppose "Bertie" and the first award will just have to stay looking unbalanced but glad you have it working. :)
 

ToucHDowN

Registered User.
Local time
Today, 16:39
Joined
May 4, 2014
Messages
36
Sorry, I seem to have missed your last couple of posts. Suppose "Bertie" and the first award will just have to stay looking unbalanced but glad you have it working. :)

Thanks for everything. Especially, for the motivation to keep it up. Really! I was very lucky to find you around.

-George.
 

Attachments

  • correct_form.jpg
    correct_form.jpg
    96 KB · Views: 71

bob fitz

AWF VIP
Local time
Today, 23:39
Joined
May 23, 2011
Messages
4,717
Thanks for everything. Especially, for the motivation to keep it up. Really! I was very lucky to find you around.

-George.
Thank you for your kind words. Such comments are always much appreciated, but there's usually someone here who's willing to help with dogged persistence. Just sorry it took so long to arrive at a happy ending.
Good luck with your project.
 

Users who are viewing this thread

Top Bottom