Limiting the number of available records in a table, and recycling/reusing them...?

[ajetrumpet]

P.S.
Craig, I am working on the Prius for ya

Well that's wonderful, but what about mine!?

 

[Colin@Toyota]

Well that's wonderful, but what about mine!?

You never asked!

 

[ajetrumpet]

Consider the previous post the request smarta**.

 

[Colin@Toyota]

I think the part that is screwing me up is that the values I need to appear in the combo boxes aren't all from one table. I can't seem to logically work out how the SQL should be that must be included in the code for the AfterUpdate event.

 

[ajetrumpet]

I think the part that is screwing me up is that the values I need to appear in the combo boxes aren't all from one table. I can't seem to logically work out how the SQL should be that must be included in the code for the AfterUpdate event.

Are you talking to me Colin? I haven't even looked at your database! If you're referring to my earlier points about SQL, those are just rules that can be used by anyone, in any situation (unless of course you've seriously screwed up your database structure!!).

 

[Colin@Toyota]

Are you talking to me Colin? I haven't even looked at your database!

I'm talking to anyone who will listen!

Do you feel like taking a look at it?

 

[ajetrumpet]

Do you feel like taking a look at it?

What do I get in return?

 

[Colin@Toyota]

What do I get in return?

Mostly gratitude, with some thanks, and the knowledge that you are helping someone learn about a subject in which you are more proficient.

 

[CraigDolphin]

Colin,

here's your db with the form functional.

There were several problems to deal with but the root cause of your difficulties is that you were trying to use two controls to each fill in partial information needed to determine which modelID to store in the table without actually creating a control to hold the ModelID (as opposed to just the modelname or just the model year). So, if you look at the form in design view you'll not an additional text box control (txtModelID) that holds the actual modelID.

You also lacked a VDSID field in the tblSETRModels to store the vds code you wanted to associate with this setr/modelname/modelyear combination.

The two combos you were using before are now unbound. That means that you need to fill them each time you move to a new record (look in the on_current event, or whenever you change one of the values in the unbound controls). The value in txtModelId is determined in the after_update event of both of your unbound combos (but only when both have been populated).

If you try to enter the year when the model name is not entered, or if you try to enter the vds when either model name or year has not been entered, the form sets the focus back to the model name.

I also fixed the underlying problem in the setrmodelparts subform that caused the Error message to appear. This was due to a query field not being 'fixed' when you played about with the WebportalItems table.

Craig, I am working on the Prius for ya

Excellent I'll expect it about the time Adam receives my cheque (check for those who prefer not to spell things correctly)

 

[CraigDolphin]

Oh...I forgot to link the VDSID field in tblSETRModels to the VDS_ID field in tblVDSs in the relationship view and enforce referential integrity etc. You should probably do that too.

 

[ajetrumpet]

Excellent I'll expect it about the time Adam recieves my cheque (check for those who prefer not to spell things correctly)

You've done more work than I have here Craig. For that, I "pre-deposit" my cheque in your account.

 

[Colin@Toyota]

Oh...I forgot to link the VDSID field in tblSETRModels to the VDS_ID field in tblVDSs in the relationship view and enforce referential integrity etc. You should probably do that too.

I just noticed haha

It didn't like it when I tried to change the model

 

[CraigDolphin]

I think the part that is screwing me up is that the values I need to appear in the combo boxes aren't all from one table.

Remember combo boxes have two functions.

One is that they store one piece of information in a field in the form's record source (ie the underyling table)

Two is that they display a list of possible values to be stored, and a bunch of associated information about those values. This associated information makes the data readable by humans. The list of values and information can be drawn from many related tables, but only one field from that list can ever be stored.

So, only one table (or updateable query) is bound to the form (this is where the value selected is stored) but several tables/queries can be referenced when helping the user decide which of the possible values to choose from.

 

[CraigDolphin]

It didn't like it when I tried to change the model

What do you mean? It should clear the slate for the other options because, by changing the model, the previously selected items might no longer be valid choices.

 

[CraigDolphin]

For that, I "pre-deposit" my cheque in your account.

Great. That, and $4.50 will buy me a triple-shot 20 oz Mocha

 

[ajetrumpet]

$4.50 will buy me a triple-shot 20 oz Mocha

Well, it sure won't buy you a Prius, but coincidentally, it will buy you a tank of gas for it!

The list of values and information for combo boxes can be drawn from many related tables, but only one field from that list can ever be stored.

Very few people understand this phenomena, and I'm actually surprised you said it!

 

[neileg]

Two is that they display a list of possible values to be stored, and a bunch of associated information about those values. This associated information makes the data readable by humans. The list of values and information can be drawn from many related tables, but only one field from that list can ever be stored.

Not really 100% true. There is only one bound field for a combo, but it's quite possible to use, say, the AfterUpdate event to populate other fields from the combo.

 

[CraigDolphin]

Not really 100% true. There is only one bound field for a combo, but it's quite possible to use, say, the AfterUpdate event to populate other fields from the combo.

Of course I was meaning one field can be stored (bound) by that combo control. You are, of course, correct about being able to populate other controls/fields with information from the remainder of the row source sql fields. Thanks for catching that Neil.

Very few people understand this phenomena, and I'm actually surprised you said it!

Not quite sure why you're surprised. Surprised I understand it? Surprised I screwed it up? Surprised I can articulate a concept in less than three chapters of a novel?

 

[Colin@Toyota]

Craig,

There seems to be some sort of problem still with those cascading combos. When I select a model, only one year shows up in cboModelYear, then VDS shows no options.

I was going to try using some sample code for cascading controls that Adam posted a while ago, but I'm not sure about some of the SQL. It's something along the lines of:

Private Sub cboModelName_AfterUpdate()
     Me.cboModelYear.RowSource = "SELECT tblSETRModels.ModelYearID FROM tblSETRModels WHERE " & _
     "tblSETRModels.ModelNameID = [cboModelName]"
End Sub

Private Sub cboModelYear_AfterUpdate()
     Me.cboVDS.RowSource = "SELECT tblSETRModels.VDS_ID FROM tblSETRModels WHERE " & _
     "tblSETRModels.ModelYearID = [cboModelYear]"
End Sub

Now for a couple questions...

In the SQL there, do I want to be calling the ID fields, the text fields, or both? I noticed you have set the RowSource for each combo box to both related fields (for ModelName = ModelName, and ModelNameID; ModelYear = ModelYear, and ModelYearID, etc.) As I type this, it seems to make sense that both fields need to be referenced in the SQL... is this done with comma's?

Also, I was thinking about potential cases where it might not be necessary to select a single model... would it be best to simply add to tblModelNames a record that is called "Various" or "All Model" that is then linked to every year, and where the VDS = *****? Or would there be some way to code something like:

IF cboModelName IsNull, SELECT DISTINCT tblSETRModels.ModelYearID FROM tblSETRModels?

to accomplish the same thing?

Let me know if it seems like I am trying to make this more difficult than it needs to be.

 

[CraigDolphin]

Colin,

I'm not going to try dissecting Adam's solution. He'll have to earn his prius himself

You're right though...there was an error in the sql for cboModelYear (was using the criteria for modelnameid in the modelid field accidentally.

Change it to the following:

SELECT tblModelYear.ModelYearID, tblModelYear.ModelYear
FROM tblModelYear INNER JOIN tblModels ON tblModelYear.ModelYearID = tblModels.ModelYearID
WHERE (((tblModels.ModelNameID)=[Forms]![fmSETR]![subformSETRModels].[Form]![cboModelID]))
GROUP BY tblModelYear.ModelYearID, tblModelYear.ModelYear
ORDER BY tblModelYear.ModelYear;

Now, you've only got VDS codes listed for 18 models of vehicle in the VDSModels table. Remember, a model is a unique combination of model name and model year (ie 2003 Corrolla). If you really intend for VDS codes to apply to model name (ie, corolla) then we need to do a little restructuring.