Referencing current record 2

[csroberson]

Please forgive me if this question is misplaced or malformed but this is my first foray into MS Access and SQL and I haven't done anything with relational databases since about 1985. OK, with that caveat out of the way, please allow me to ask my question:

I am working on a SQL query for a Row Source field of a Combo Box in Microsoft Access 2000. I want to show a drop down list of available values from a table called tblConfigurations:

{ConfigID, PlaceID, LocalConfigName, ...}

This is going into a table called tblEvents:

{EventID, Date, HostID, PlaceID, ConfigID, ...}

As I populate tblEvents, a date is entered, a Host is selected (from a list based on tblHosts) and a Place is the selected (from a list based on tblPlaces) and now I come to my issue.

I want to create a list of {ConfigID, LocalConfigName} but only if the PlaceID in tblConfigurations matches the PlaceID just selected for the previous field of the record being created. There is no reason to show Configs for other places -- there can be multiple configs for each place.

So far, I have the following select:

SELECT [tblConfigurations].[PlaceID], [tblConfigurations].[ConfigID], [tblConfigurations].[LocalConfigName] FROM tblConfigurations;

It seems I want to use a WHERE clause but I don't know how to reference the PlaceID field of the current record. I'm wondering if I'm thinking about this the wrong way.

I'm looking for suggestions, examples or web page references. Any help will be appreciated.

Thanks,
Chip

 

[dhookom]

Check Roger Carlsons sample of Cascading Combo boxes at

http://www.rogersaccesslibrary.com/TableOfContents3.asp#C

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[csroberson]

Thanks Duane,

That helped. I had already found the forms reference for the current data but your reference helped me find the "after update" event procedure:

Private Sub Track_ID_AfterUpdate()
Me!ConfigID = Null
Me!ConfigID.Requery
End Sub

However, I had to set the "Required" attribute for ConfigID to "No." I now need to make sure a value is selected before the next record is accessed. I guess there are two ways to do this (e.g set a default but valid value) or put in some kind of validation, I'd be happier with the easiest solution at this time.

Any help?

Thanks,
Chip

 

[csroberson]

Argh, this is turning out harder than I thought. As I scroll through records, the displayed ConfigID field keeps getting set to NULL or it remains the same value as the last displayed ConfigID.

I'm obviously going to need more work. Hmmm.

Thanks,
Chip

 

[dhookom]

When I do this with a continuous form, I include all possible values but sort them so the valid options are at the top. I may even add a column to the combo box with values like "valid" or "not valid". I can then use the after update event of the combo box to make sure the right most column is "valid".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[csroberson]

Duanne, I appreciate you taking the time to respond to my questions. I have to admit that I'm struggling a bit to fully understand your message.

To restate my situation, I basically have to figure out the following:

1) When I cycle to an existing record, I want to show the stored value for ConfigID, not NULL nor the last value displayed;
2) When I leave a record, I want to make sure ConfigID is not NULL but set to valid value.

I think your last post was responding to the second question but I'm not quite sure. I think the idea is that you set the extra column value only after the user has selected a value and you check for that on exit?

Do you possibly have a code example I could use as reference (as I'm also new to Visual Basic -- I assume that's what this these procedures are -- as well as MA and SQL). I'm an old C programmer.

Thanks,
Chip

 

[csroberson]

Sorry to keep replying to my own messages but I thought I should mention, if it isn't already obvious, that the form is (or its contents are) bound to a specific table. I use this form to input and review the data. Roger Carlson's examples all seem to be for unbound forms/fields.

Regards,
Chip

 

[csroberson]

Using a Google search for "continuous form" and "cascading combo" I found the following on Access Monster:

More complex in ContinuousFormView since what you see is many instances of
the *same* ComboBox (and therefore only ONE RowSource) while you need
*different* RowSources for the different instances of the ComboBox.

A work-around is to use *two* ComboBoxes, says, "cboUnfiltered" and
"cboFiltered". Use an unfiltered RowSource for the cboUnfiltered (every
possible values in this RowSource) and use a filtered / parametrised
RowSource for the cboFiltered.

Size the 2 ComboBoxes to be exactly the same and bring cboUnfiltered in
front of cboFiltered. Use the cboUnFiltered_GotFocus Event to set the Focus
onto the cboFiltered.

This way, in the non-current rows (and the current row when the Focus is on
some other Control), the user will see the cboUnfiltered which will display
correctly (since all possible values are in the RowSource). As soon as the
user put the Focus into cboUnfiltered, the GotFocus Event will send the
Focus to the cboFiltered. This will make the cboFiltered visible and the
user can only select a value from the *filtered* list.

Does these seem like a reasonable approach? If anyone can point me to some example code, that would be appreciated.

Thanks,
Chip

 

[dhookom]

I would set the required property in the table design.

To maintain the combo box display. I created a form "frmCascadeCombo" in the Northwind database. I added two combo boxes:
Combo box to select product category
Name: cboCategories
Row Source: SELECT [CategoryID], [CategoryName] FROM [Categories]
Column Count: 2
Bound Column: 1
Combo box to select product based on category
Name: cboProduct
Row Source: SELECT ProductID, ProductName,
IIf([CategoryID]=[Forms]![frmCascadeCombo]![cboCategory],"Valid","Invalid") AS Valid
FROM Products
ORDER BY IIf([CategoryID]=[Forms]![frmCascadeCombo]![cboCategory],"Valid","Invalid") DESC , ProductName;
Column Count: 3
Column Widths: 0,2,.5
As you move through records or change the value of cboCategory, the product combo box needs to be updated. You can do this with code in the form module:

Sub RequeryProducts()
    Me.cboProduct.Requery
End Sub

You can then call this procedure from both the On Current and the After Update of cboCategory:

Private Sub cboCategory_AfterUpdate()
    RequeryProducts
End Sub
Private Sub Form_Current()
    RequeryProducts
End Sub

You can also add a message if the user selects an invalid product based on the category in the after update event of the product combo box:

Private Sub cboProduct_AfterUpdate()
    If Me.cboProduct.Column(2) <> "Valid" Then
        MsgBox "You selected an invalid product"
    End If
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[csroberson]

Duane, I really appreciate the time you are taking to help me on this problem. It's useful and educational. Right now I'm just using a very basic Access book, online help and what I can find on the web to help me. Can you recommend a good book (or set of books) for reference regarding Access, SQL and VB?

You made reference to the Northwind database. Should I be familiar with that and know where to find it?

There are some differences between the "Row Source" selects for our first combo box (cboCategory or cboCategories). You have:

SELECT [CategoryID], [CategoryName] FROM [Categories]

while I have the equivalent of:

SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM Categories;

I'm believing the syntax is significant to the scope of what is selected and I think this is important because I see that you reference [ProductID] in your second combo box (cboProduct). Could you possibly clarify this for me?

Also, before you replied I found the following:

http://www.utteraccess.com/forums/access/access173402.html

which appears to be another way to address the problem of cascading combo boxes on continuous forms (though I don't fully understand it yet, either). Any thoughts on that approach?

Again, many thanks for your time.

Chip

 

[csroberson]

Nevermind on the Northwind database. I just found reference to it on the microsoft site:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209576

I have Access 2000 but Office 2003 and somethings aren't installed in Access, including the sample Northwind db.

This task has become more difficult than I originally imagined and it is quite obvious that I have a lot to learn to be even marginally effective. Thanks again for your help.

Chip

 

[dhookom]

I personally wouldn't use two combo boxes.
The two row sources are very much the same. You don't need the table name if there is only one table in the FROM clause or if your field names are unique.

ProductID is the value that I want to store in my table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[csroberson]

Thanks Duane,

I actually have your example working with my code and it suits my needs. I don't fully understand the fine details or semantics of all the operations but I understand much more than I did last night.

Your help was very useful.

Too bad this thread is now so mis-named but hopefully it will help others too.

Regards,
Chip