Not able to select from combobox

[Triel]

Hi -

Thanks for any help you may be able to provide. I am going crazy, I searched on the web and everything it says to do is already set correctly in my form.

I have a combobox that I populate on formload with:

cboGROUP.RowSource = "SELECT DISTINCT faqQA.qaGROUP FROM faqQA WHERE fkPWPKID = Text6.Value ORDER BY faqQA.qaGROUP"

and all of the correct data is in my combobox but when I test my form I can't actually select one of the options. I click on it and the combobox does not have the value in it, it is just blank like it is when the form is loaded.

I have looked all over the internet trying to find my answer. I get make sure the "Allow Edits" is set to "yes" which it is.

The field is in the header section if that makes any difference. The Control Source is blank, the Row Source is blank and the Row Source Type is set to "Table/Query".



Can someone tell me what I am doing wrong?

Thanks!

[ranman256]

Check tje combos BOUND COLUMN. It may not give what you want.

[Triel]

It is bound to column 2. I am getting the correct items in the drop down but I can't select them. As I said in my post the items in the drop down are correct. I have 2 items like I am supposed to but when I click one them the box stays blank like it does when it loads. It does not update with my selection.

[Missinglinq]

Allow Edits is set Yes, but can you actually edit a Record on it, or is the Form really Read-Only?

Linq ;0)>

[June7]

The RowSource has only 1 field - why is the BoundColumn property set to 2?

Why do you set the RowSource with VBA? Why not just have the SQL statically in the RowSource property?
SELECT DISTINCT qaGROUP FROM faqQA WHERE fkPWPKID = [Text6] ORDER BY qaGROUP;

Have code in the combobox GotFocus event to requery the RowSource so it remains in sync with Text6.

[Missinglinq]

The RowSource has only 1 field - why is the BoundColumn property set to 2?

Good catch! That, of course, will result in exactly the problem you're seeing!

Setting the RowSource in the Form_Load event, based on the value in Text6, means that it will always be based on the value of Text6 in the first Record that displays, not the current Record. Either Requery the Combobox in the GotFocus event of the Combobox, as June7 suggested, or in Form_Current event.

Linq ;0)>

[June7]

I suggest the combobox GotFocus event in case user changes value of Text6. The form Current event won't accommodate that occurrence.

However, if user changes Text6, should the data in the combobox field be deleted so user must select another value associated with the new value in Text6?

[Missinglinq]

...I suggest the combobox GotFocus event in case user changes value of Text6. The form Current event won't accommodate that occurrence...

True!

...if user changes Text6, should the data in the combobox field be deleted so user must select another value associated with the new value in Text6...

Good point!

Linq ;0)>

[Triel]

I suggest the combobox GotFocus event in case user changes value of Text6. The form Current event won't accommodate that occurrence.

However, if user changes Text6, should the data in the combobox field be deleted so user must select another value associated with the new value in Text6?

Ok, am I doing this all wrong? See really Text6 is just a hidden field because I was playing around and I couldn't get the value that is assigned to it in query to make it work correctly. I put it in Text6 to see if it was coming in correctly, which it is.

Here is my frmLogin code, this is the 1st screen where they log in. Depending on who is logging in I need to display a "Group" number they can pick from. Then depending on which "Group" they pick I need to display the "questions" in the detail section for that particular group. (Along with the questions they will have an edit and delete next to the questions so they can maintain the questions and answers.)

Option Compare Database
Dim intLogonAttempts As Integer

Private Sub Form_Load()
    Me.cboDept.SetFocus
    Me.cboDept = Null
    Me.txtPassword = Null
End Sub


Private Sub cmdLogin_Click()
'Check that Dept is selected
If IsNull(Me.cboDept) Then
   MsgBox "You must enter a Department Name.", vbCritical
   Me.cboDept.SetFocus
Else
   'Check for correct password
   If Me.txtPassword.Value = DLookup("pwPW", "faqPW", "[pwPKID]=" & Me.cboDept.Value) Then
       lngPKID = DLookup("pwPKID", "faqPW", "[pwPKID]=" & Me.cboDept.Value)
       DoCmd.OpenForm "frmQA"
       Me.Visible = False
   Else
       MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
       Me.txtPassword = Null
       Me.txtPassword.SetFocus
   End If
End If

'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
        MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
        Application.Quit
    End If


End Sub

Here is the code in my frmQA.

Option Compare Database

Private Sub cboGroup_GotFocus()
    cboGROUP.RowSource = "SELECT DISTINCT faqQA.qaGROUP FROM faqQA WHERE fkPWPKID = Text6.Value ORDER BY faqQA.qaGROUP"
End Sub

Private Sub Form_Load()
   Text6.Value = lngPKID
   'cboGROUP.RowSource = "SELECT DISTINCT faqQA.qaGROUP FROM faqQA ORDER BY faqQA.qaGROUP"
  'cboGROUP.RowSource = "SELECT DISTINCT faqQA.qaPKID, faqQA.qaGROUP FROM faqQA WHERE fkPWPKID = Text6.Value ORDER BY faqQA.qaGROUP"
  cboGROUP.RowSource = "SELECT DISTINCT faqQA.qaGROUP FROM faqQA WHERE fkPWPKID = Text6.Value ORDER BY faqQA.qaGROUP"
  Me.cboGROUP.SetFocus
   
   
End Sub

The field "lngPKID" is defined in a module so that all forms will have access to it.


Here are my tables in case you need them to understand. The departments can have many groups, the sort is just used to put the questions in order.
And just to make it even clearer I will attach my mock up. On my 2nd page though the drop down box says "Select FAQ Number", that is really "Group".

I have the drop down, the add question and the exit all in my header. I was going to put the questions with the edit buttons in the detail section. I haven't gotten that far yet. I am just at the point of making sure the drop down works. (Note: I am a bit confused on how to do the detail section. I was hoping to figure it out.)

I just wanted to get the drop down to work correctly before going any farther.

I haven't done access in many years and this is a lot harder than I had thought. I keep looking things up and it has gotten me this far but I am stuck.

[June7]

Did you try the SQL in the RowSource property? Did you change the BoundColumn property?

Will each user be associated with only one department? If so, why make user have to choose?

Is faqPW table for user info records? Code can pull the info from faqPW table based on user ID.

How does code know who the user is? For one approach, review https://www.accessforums.net/access/...see-46984.html

Perhaps you can tell us a little more about purpose of this db.

[Triel]

Did you try the SQL in the RowSource property? Did you change the BoundColumn property?

Yes, I tried that and it was not working. I played around with it and once I added the qaPKID to it, it seems to work. The only problem I have is that I do a select "distinct" and since it has both columns in it I am getting multiple "groups" so what I am getting for the groups now is 1, 1, 2, 2. I only want it to display 1, 2.

I can select them in the combo box now but as I said there should only be 1 of each number.

The other question I have is why since I have the field lngPKID defined in the module so that all forms have access to the value can't I use this in the select statement?
It is currently this:
SELECT DISTINCT faqQA.qaGROUP, faqQA.qaPKID FROM faqQA WHERE (((faqQA.fkPWPKID)=[Text6].[Value])) ORDER BY faqQA.qaGROUP;

I tried to change it to lngPKID and it doesn't work.

Will each user be associated with only one department? If so, why make user have to choose?

My mockup was incorrect, sorry I did not say that. The user does not put in a user name, they select their department from the combo box and input their password. So this is how I am populating the combo box on the 2nd screen.

Perhaps you can tell us a little more about purpose of this db.

The purpose of this db is for departments to be able to maintain their FAQ that are on the web. They are supposed to log in with their department and password. This will then populate the combo box on the 2nd page with all of the groups of FAQ questions for that department. They would then select the group and that will populate the questions on the page. They can then either edit or delete the question in that group. The edit button will bring up a detail form (he wants me to have the detail form on the same form as the list of questions.)

Each department can have multiple FAQs, hence the reason for the group number. They may even just have 1 FAQ and this will have to be able to add another group. Oh my, my mockup does not take that into consideration either. I only have an add question button.

Hopefully I explained this fully.

[June7]

My point about login is that this could possibly be mostly eliminated. In my db, user info is stored in a Users table. Code grabs the user ID from network login. I accept that if user can log in to the network they are valid database user and no password for db is needed. Only a first-time user will ever see the 'login' form because they are not yet in the Users table. This first-time login creates new user record and they never see the 'login' again.

As for the combobox, I don't really understand data relationships and don't know why the 1 field RowSource with the suggested edits is not working. If you want to provide db for analysis, follow instructions at bottom of my post.

BTW, the .Value can be eliminated. Value is default property of data controls.

[Triel]

Code grabs the user ID from network login.

Oh, I wasn't getting really fancy. For all I know this isn't even going to be used. You don't know how many times I have redone this project. 1st I did it in HTML, then I did it in XML so we could update it in there. NOW he wants an access database to do it. For all I know next he will want me to write a C# program to do this. *Shrugs* I just want it to work so I can say I am done. I have not coded in access in about 20 years.

As for the combobox, I don't really understand data relationships and don't know why the 1 field RowSource with the suggested edits is not working. If you want to provide db for analysis, follow instructions at bottom of my post.

Ok, I attached it so you can take a look. I just have a text box for the question but I don't think that is how it should be. I just left off there without looking up what I should be doing since I could not select a "group" in the combo box.

Once the group combo is working depending on what they select I need to display all the questions in the detail section. Well I am not going to go over all that again, I believe I explained it above.

The data in the tables is just play data so I can see it working.

Thanks so much for your help! It is SO appreciated!

[June7]

The frmQA combobox RowSource can be:
SELECT DISTINCT qaGROUP FROM faqQA WHERE fkPWPKID=[Text6] ORDER BY qaGROUP;

No code to requery combobox is needed.

Should frmQA be bound to table faqQA?


The qryGroup has reference to a public declared variable as filter parameter. Access (queries, forms, reports, macros) cannot read regular VBA variables. Access can reference TempVars and that's a topic for another thread or you can do research. The query just treats the reference as a parameter input popup prompt and has no connection to the declared variable. The parameter needs to reference control on form.

There is no Groups table. What are groups? How many possible groups? Can any department be associated with any group? The only place were departments and groups have any association is in faqQA via the fkPWPKID field.

The frmLogin Load event code accomplishes nothing. Why bother setting frmLogin not visible? It is completely behind frmQA. Otherwise, frmLogin combobox code is working. However, the second DLookup is not needed. The pwPKID is already available in the combobox.
lngPKID = Me.cboDept

[Triel]

The frmQA combobox RowSource can be:
SELECT DISTINCT qaGROUP FROM faqQA WHERE fkPWPKID=[Text6] ORDER BY qaGROUP;

Thank you! That did fix it! It was as you said and I needed to change the "Bound Column", I coulda swore I did that and it didn't work. I might have left the code in the form load though when I did it and didn't realize it.

Should frmQA be bound to table faqQA?

I am not sure since it has been so long but I did bind it. I am going to do some research to make sure but you are probably right.

The qryGroup has reference to a public declared variable as filter parameter. Access (queries, forms, reports, macros) cannot read regular VBA variables. Access can reference TempVars and that's a topic for another thread or you can do research. The query just treats the reference as a parameter input popup prompt and has no connection to the declared variable. The parameter needs to reference control on form.

Thank you for explaining this, I understand more. I will do some research on this too.

There is no Groups table. What are groups? How many possible groups? Can any department be associated with any group? The only place were departments and groups have any association is in faqQA via the fkPWPKID field.

Well, the guy here who is making me do this and helped with the design didn't include a "Groups" table. Groups is the number each group of FAQ questions. Maybe a Groups table is required. Each department can have multiple groups of FAQ questions. Say we have Register of Deeds (Register of Deeds is a department) - they have 2 groups of FAQ questions, 1 - Land Description, 2 - Vital Records. Then you have Treasurer - they have 2 groups, 1 - Taxes, 2 - Foreclosures. Etc.... The groups will not cross departments. The number of groups can be unlimited, departments will be able to create a group such as if they currently have only 1 FAQ but want to split it up into 2 or more.

Am I creating this wrong? It seems like it will work as it is. Although there is no title for each group. (There is actually a title for each group that will need to be able to be edited and these are PNG image files. The guy here said that I should just code this as I am doing and it will be "EASY" to add the editing of the image after I finish this part.)

The frmLogin Load event code accomplishes nothing. Why bother setting frmLogin not visible? It is completely behind frmQA. Otherwise, frmLogin combobox code is working. However, the second DLookup is not needed. The pwPKID is already available in the combobox.
lngPKID = Me.cboDept

Thank you for this, I did take out the 2nd DLookup as you suggested. It makes sense why do another one when it is already available. I did test this form before moving on. I want to get this working piece by piece.

You did open my eyes to a problem in the design though, I need to have a button to be able to "Add Group". I will have to change the button that says "Add Record" to "Add Question" so it is clearer.