Listbox to populate subform problem

[magster06]

Hello all,

I am trying to populate my subform from a listbox selection.

The problem is that I can see where there are 10 records in the subform navigation bar, but no records are visible.

Here is the code from the listbox on click event:

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strCWRFieldSelected as String
 
On Error GoTo ErrrorHappened
 
If lstCWR.ListIndex <> -1 Then
strCWRFieldSelected = lstCWR.ItemData(lstCWR.ListIndex)
 
Set db = CurrentDb
 
strSQL = "SELECT '" & strCWRFieldSelected & "' FROM CWRVerbiage"
 
Set sfCWRValues.Form.Recordset = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
Else
 
GoTo ExitNow
 
End If
 
ExitNow:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Sub
 
ErrorHappened:
MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
Resume ExitNow
Resume

 

[MarkK]

I would break into the debugger at this line ...

Set sfCWRValues.Form.Recordset = db.OpenRecordset(strSQL, dbOpenSnapshot)

Then type ...

? strSQL

... in the immediate pane. Copy that SQL statement into the query designer, and see what kind of recordset you are actually producing.

With the single quotes in there, you are actually pushing in a literal string, and I expect that SQL parser is treating it like data, and assigning it's own field name, like Expr1001, or something, and since you draw from the Verbiage table without criteria, it will show you all the records, but you have not named a field to retrieve that actually exists in that table.
Does that make sense?

 

[magster06]

lagbolt,

Thanks for the response to my issue.

I opened the immediate window and the sql seems to be pulling what I want. I only have one record for each field.

Here is what the sql is pulling:

SELECT 'CWR1(B)' FROM CWRVerbiage

the CWR1(B) is the selection from the lstbox (lstCWR).

I tried to add a criteria, but I am not sure how to add a listbox to it.

SELECT 'CWR1(D)' FROM CWRVerbiage WHERE CWR1(D) = 4

and this the sql :

strSQL = "SELECT '" & strCWRFieldSelected & "' FROM CWRVerbiage WHERE " & strCWRFieldSelected & " = " & lstCWR.ListIndex & ""

I know I shoud not be using listindex as it will only give me the number of what I am after, but I am not sure what to use.

 

[magster06]

Where would I find sql designer in access 2010?


FOUND IT, lol!

lagbolt you are correct; this is what the sql designer is showing:

Expr1000=CWR1(H) over and over (10 of them)


Question is: how do I correct this?

 

[magster06]

Ok, this is what I have changed and it is still NOT working, argggggh!

strSQL = "SELECT CWRVerb.Verbiage FROM CWRVerb WHERE CWRVerb.CWR = """ & strCWRFieldSelected & """"

I placed this code in the query designer and it comes back with the desired results, but the record is still not visible in the subform.

The subform shows that a record is there, but not visible.

Any ideas?



I can get it to work for a listbox, but there is no word wrap for a listbox, that is why I need the subform.

 

[MarkK]

You don't need quotes in SQL. Anything you enclose in quotes in SQL is interpreted as literal string data, but you need to make sure you reference a field that actually exists too.

SELECT *
FROM YourTable
WHERE strFieldInYourTable = 'StringLiteral'

In a WHERE clause you might compare a string literal to a field in your table.
hth

 

[magster06]

lagbolt,

Here is a pic of my table where I am trying to extract the record from to populate my subform

I am using the following and it works in the query designer. So why in the world will not populate my subform?

Do I have my subform setup incorrectly?

strSQL = "SELECT CWRVerb.Verbiage FROM CWRVerb WHERE CWRVerb.CWR = '" & strCWRFieldSelected & "'"

and this is the sql it shows in the immediate window:

SELECT CWRVerb.Verbiage FROM CWRVerb WHERE CWRVerb.CWR = 'CWR1(B)'

Here is the entire code for pulling the info from the CWRVerb table and populate the subform:

 Dim strCWRFieldSelected As String
    Dim db As DAO.Database
    On Error GoTo ErrorHappened
    Dim rs As DAO.Recordset
    If lstCWR.ListIndex <> -1 Then
        strCWRFieldSelected = lstCWR.ItemData(lstCWR.ListIndex)
    
        Set db = CurrentDb
        strSQL = "SELECT CWRVerb.Verbiage FROM CWRVerb WHERE CWRVerb.CWR = '" & strCWRFieldSelected & "'"
        Debug.Print strSQL
        Set sfCWRValues.Form.Recordset = db.OpenRecordset(strSQL, dbOpenSnapshot)
    Else
        GoTo ExitNow
    End If
ExitNow:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
ErrorHappened:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume ExitNow
    Resume

 

[magster06]

Is there any hope for me, lol?

Any ideas, anyone?

 

[MarkK]

If you post a stripped down database that demonstrates the problem, I'll take a look.
One thing is that Access might have added it's own linking info when you created the subform. Check that there is nothing present in the LinkMasterFields and LinkChildFields properties of the subform control. Those properties are available on the Data tab of the subform control's property sheet in design view.

 

[magster06]

lagbolt,

I cannot get the database small enough (to meet the 2mb maximum) and still function.

 

[boblarson]

lagbolt,

I cannot get the database small enough (to meet the 2mb maximum) and still function.

Did you remember to run Compact and Repair first and then right click on the file and select SEND TO > COMPRESSED FOLDER and check that file size?

 

[magster06]

Hi Bob,

Yes, I did the compact and repair.

I then removed 3/4 of the project and then zipped it with winzip, but it was still too large.

I then removed more of it, then it started to malfunction big time.

 

[magster06]

Ok, I created another project and just included the main form with the listbox and subform on it.

 

[MarkK]

This is working. What is the problem?
It's a bit unusual to me to have a subform when you have only one table. Typically a subform makes sense when you have an object, like an invoice, that has many other objects, like invoice detail records, that belong to the parent object. So when you show the parent in the main form, showing the children is what you'd expect in the subform.
What you have done is driven a list on a main form from a single table, and selecting an item in the list shows that item in the subform. That is more simply implemented like what you'd find in the database I posted, (check the LinkMaster and LinkChildFields of the subform control) but it's not necessary, since you could just as easily show the selected record in the main form, without the need for a subform.
Hope this helps, and thanks also to Bob.

 

[magster06]

lagbolt,

If the project works for you, then there must be something wrong with my version of Access 2010.

It will not work for me at all. I will try to reinstall the program and see what happens.

As far as using just one table to populate the subform; I wanted to display the info in a dataview format and as far as I can tell Access does not have a datagrid to do this.

I am using the listboxes as queries pickers and the subform to display the info.

So, from what I have read, the subform is the next best thing for this.

 

[magster06]

lagbolt,

Thanks for the sample that you have posted.

I have already tried that, but I wanted to just use the subform by itself.

I forgot to mention (in my last post) that in my real project I have several tabs on my form with listboxes that populate the subform with several records.

Anyway, THANK YOU, for taking to the time to help me!

 

[MarkK]

...with listboxes that populate the subform with several records.

If you do this, there is no need in the parent form to open a recordset and assign it to the recordset property of the subform. Rather, you can simply set the LinkMasterFields and LinkChiledFields properties of the subform control, which is far simpler.