Using A combo box to select record source for a form

[Jellyhawk]

Hello everyone, I am new here and new to database developing so I am naturally facing a bit of a learning curve. Although I do have a basic understanding of VBA

Anyhow I have battled my way through importing some excel data into a new database and it seems to be working as intended.
I have set up an input form to populate the various fields and Normalised the data into three tables. The database is for 'Equipment Assets' for a chemical plant.

The tables are organised by 'Process Circuit', 'Asset Type' and then a table for the main equipment data called 'Main'.

I have set up a number of queries to list the data by 'Process Circuit'
The data input form seems to work fine. I would like to set up a read only 'Viewing' form based upon it, that only displays the data from the queries.

So, I have an intro form with controls to select 'Input' which loads the input form or 'View', which I would like to load another form with a list box to select which query to use as record source for the 'View' form.

Trouble is I don't have a clue how to do it! I can do it by having a separate form for each circuit but I know this is the wrong approach and would result in 50+ identical forms

I would be grateful if someone could point me in the right direction.

Apologies for the long post.

Many Thanks.

 

[CBrighton]

I assume you use VBA (or the macro equivalent) to open the form currently?

Look at the 4th variant of OpenForm:

[I]expression[/I].[B]OpenForm[/B]([B][I]FormName[/I][/B], [B][I]View[/I][/B], [B][I]FilterName[/I][/B], [B][I]WhereCondition[/I][/B], [B][I]DataMode[/I][/B], [B][I]WindowMode[/I][/B], [B][I]OpenArgs[/I][/B])

Therefore:

dim strWhere as string
strWhere = cboQuerySelection
DoCmd.OpenForm("frmViewProcessCircuit",acNormal, , strWhere)

Note, I used a combobox because I see no point of a single select listbox. Plus I know that the implicit .value works fine on comboboxes but I can't recall if listboxes need something else like .SelectedItem (It's been a while since I used a list box ).

 

[missinglinq]

Listboxes with their Mutli Select Property set to None do, indeed, have a Value Property.

It's only when their Mutli Select Property is set to Simple or Extended that they have no Value.

It should be noted that starting with 2007, I believe, Comboboxes also have a Multi Select Property, which I assume works exactly like Listboxes.

And a word of warning for Jellyhawk: If your 'basic understanding of VBA' comes from working with straight Visual Basic, or VBA for another app, such as MS Excel, be aware that while these flavors of VB/VBA come from the same original source language (QuickBasic 4.5) they are distinct 'dialects,' if you will, and many things, especially Functions and Properties, work differently. Just keep that in mind.

And welcome, Jellyhawk, to AWF!

Linq ;0)>

 

[vbaInet]

It should be noted that starting with 2007, I believe, Comboboxes also have a Multi Select Property, which I assume works exactly like Listboxes.

Hey missinlinq,

I just had a second check and 2007 doesn't have the Multi Select property. Maybe your assumption is true for 2010. Unfortunately, I don't have it on this laptop to test.

 

[Jellyhawk]

Many thanks folks,

I will give your suggestions a whirl and let you know how I get on.

I dare say I will be back with more questions...

Thanks again.

 

[Jellyhawk]

Hello again everyone,

I am still struggling with to get this working

Here is what I did, I have a combo box called 'CircSelCombo' to select the Query I want the next form 'Main-small-read-only' to use as a record source.

I then added a button to the same form as CircSelCombo. I set the 'Click event' for this button to load the required form and added the suggested code to the VBA to modify the 'where' condition for the form.

When I click the button the form loads without changing the record source to that selected.

Have I misunderstood? Or just got it wrong

Private Sub OpenReadOnly_Click()
On Error GoTo Err_OpenReadOnly_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim strwhere As String
    strwhere = CircSelCombo
 
 
 
    stDocName = "Main-small-read-only"
    DoCmd.OpenForm stDocName, acNormal, , strwhere
Exit_OpenReadOnly_Click:
    Exit Sub
Err_OpenReadOnly_Click:
    MsgBox Err.Description
    Resume Exit_OpenReadOnly_Click
 
End Sub

Apologies for my amateurish naming conventions I really should change those

Many Thanks,

Jellyhawk

 

[CBrighton]

To confirm, the Openform command is expecting an SQL string to apply as an additional where clause on top of the existing record source for the form which is being opened (the WHERE clause without the WHERWE keyword, e.g. "JoinDate > Date()")

It's to apply an additional filter, not to control the recordsource.

If it's the recordsource you want to change (i.e. open the same form with the same controls but with a different query behind it) try this:

Private Sub OpenReadOnly_Click()
 
DoCmd.OpenForm "Main-small-read-only"

Forms!Main-small-read-only.RecordSource = CircSelCombo
 
End Sub

-'s in the form name may need it to be enclosed in [square brackets], it's not something I've ever tried.

However, note that the bound field properties of each control will not change so if your queries use different field names you will have problems.


Personally I prefer to use filter though, you can set any criteria common to all your queries as the recordsource (while returning all fields) and apply the rest of the criteria which the query would normally apply as the filter.

 

[Jellyhawk]

Many Thanks CBrighton,

That did the trick, I did have to re-name the forms to a more sensible convention to avoid having to square bracket the code.

I intend doing an exercise in renaming all my objects to a more standard naming convention. I was a very rusty with VB when I started this project and kind of forgot the importance of naming things properly Oh well you live and learn.

Thanks again for your help and speedy response.