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

[CraigDolphin]

Colin,

honestly I'm not sure why you're getting that error. I think maybe there's an object somewhere in your db that is becoming corrupted. I've uploaded the version I have that works, and I've added to it the data that you had populated in the Models, VDS, and VDSmodels tables.

One thing I had noticed was that you had deleted the placeholder record that I had originally placed in the SETR table and with the comment DO NOT DELETE in the SETRNumber field. This is actually necessary to retain and won't show up in any of the forms/queries that I've built.

At some point you must have renamed the tables because I downloaded my original db and none of the tables had a 'tbl' prefix. You have fixed most of the Dlookups etc to find values but you missed one in the AssignLineNumber function (now fixed).

You also did not have the rowsource sql for the first combo on the webportalitem form adjusted for the change in table names. (Now fixed).

You still need to add parts to the parts table, and populate the modelparts table, as well as create forms to allow new parts and model-part combinations into the relevant tables from the forms. Then, once it's all populated you need to put this thing through its paces and see if you can break it. Then debug etc. You're going to have a busy week.

 

[ajetrumpet]

Ya know,

I was just thinking about how many files have been uploaded in this thread. And, you guys are one of the big reasons why this site is so da** slow!!

Is it about time that someone step in here!? (I know you don't need corrections Craig, just give you the sh**, but not Colin, he really needs the help!)

And BTW Colin, if you need a method to crash your computer, just say so. I am a master at OS process knowledge. Just let me know when you get sick of this, and I'll tell you what process shuts down your Boot.Ini file. If you don't know what that is, just consider it an absolute necessity when you hit your power button to turn the machine on!!

And do me favor, don't start looking for it. Because, if you do, and you screw it up, I'll be to blame. And that'll mean no more database work for you!

 

[CraigDolphin]

Is it about time that someone step in here!?

Might be handy...it's getting to the point where I've forgotten what it was that I originally did!

 

[ajetrumpet]

it's getting to the point where I've forgotten what it was that I originally did!

Craig, I'm not surprised to hear that. Do you know how many times I have had to scroll back through threads when I am helping someone just to find out the original question that was asked!?

I wonder what causes tangents like this...??

1) No explanation of the problem
2) Little explanation of the problem
3) Lack of knowledge of the problem
4) Thinking there is no problem
5) Thinking you need less help than you actually do
6) Trying to solve the problem yourself (before asking for help)
7) Experts having no knowledge of the appropriate fix
8) "Experts" thinking they're experts
9) Fantasizing about fascinating database ideas while trying to correct the problem at hand
10) Not understanding a computer in general
11) Not being passionate about what you're doing
12) Not caring about what you're doing
13) Just doing your job when you have to, because you'll get fired if you don't
14) Hailing from Canada.
15 Biologists mascarading as database professionals.

 

[CraigDolphin]

The answer is 16.

16. Not paying a professional to do it for you.

 

[ajetrumpet]

Nice answer, smart a**

 

[Colin@Toyota]

one last question before I'll give you guys some time off for the holidays...

I have a table (tblDeptCode) with about 190 records, and two fields (DeptCodeID, DeptCode). I would like a warning to pop up if the user selects a DeptCode that has already been assigned to an SETR. Would the best way to do this involve a CaseIf function? or an Array?

Also, in addition to the warning, I would like to have it display or select the next available DeptCode.

Lastly, in this table, there are 20 specific records or so that I want to allow to be assigned to multiple SETRs.

Should I just apply the CaseIf (or whatever) to the 170 or so that I don't want to have multiple SETRs?

Thanks!

 

[ajetrumpet]

one last question before I'll give you guys some time off for the holidays...

I don't need time off Colin, I haven't been helping you here. This is much too complicated for me.

 

[CraigDolphin]

First up....do you mean you want the warning to show up if the code has been allocated to any other SETR, ever? Or just if the code has been allocated to a currently active SETR?

The first part of this problem is relatively straight forward. Add a yes/no field to the deptcodes table to indicates whether the code can be used multiple times or not.

Then, in the after_update event of the deptcode combo control, you lookup whether the chosen code allows multiples, if so then just accept the value.

If not, then you need to look for that code's id in tblSETR (or alternatviely a query of SETRs that need to be examined for previous instances of the code) to see if it has been assigned previously. If you find the relevant Id, then show the warning and reject the value.

If you don't find it then accept the value.

You could accomplish all of that will domain aggregate functions like dlookup, dcount etc.
However, the next bit that you want, to prompt the user with the next available dept code, requires a different approach because the order of records then becomes important.

For that I think you need to use a recordset to accomplish your goal. Before I can help you on that I need to know a bit more about what you mean by 'the next available code'. How does one decide which of all the 'available' codes is the correct one to use? Ascending alphabetical order? Do you want the suggestions limited to only those codes that do not allow multiples?

 

[Colin@Toyota]

First up....do you mean you want the warning to show up if the code has been allocated to any other SETR, ever? Or just if the code has been allocated to a currently active SETR?

Sorry, I should have been more specific. I want the warning to show up if it is already allocated to a currently active SETR.

The first part of this problem is relatively straight forward. Add a yes/no field to the deptcodes table to indicates whether the code can be used multiple times or not.

Then, in the after_update event of the deptcode combo control, you lookup whether the chosen code allows multiples, if so then just accept the value.

If not, then you need to look for that code's id in tblSETR (or alternatviely a query of SETRs that need to be examined for previous instances of the code) to see if it has been assigned previously. If you find the relevant Id, then show the warning and reject the value.

If you don't find it then accept the value.

Makes sense.

However, the next bit that you want, to prompt the user with the next available dept code, requires a different approach because the order of records then becomes important.

For that I think you need to use a recordset to accomplish your goal. Before I can help you on that I need to know a bit more about what you mean by 'the next available code'. How does one decide which of all the 'available' codes is the correct one to use? Ascending alphabetical order?

I suppose the best way to do it would be by DeptCodeID. Maybe set it up in sections that are defined by the first letter of the DeptCode, but use the DeptCodeID as the criteria. So for example, DeptCodeIDs 1-36 all start with 'A', and if a user chooses a currently active DeptCode starting with 'A', it will find the next un-assigned DeptCode in that range. DeptCodes starting with 'B' would have IDs ranging from 37-63, etc.

Do you want the suggestions limited to only those codes that do not allow multiples?

Yes.

 

[Colin@Toyota]

I don't need time off Colin, I haven't been helping you here. This is much too complicated for me.

Yeah but I bet you stay up real late thinking up all those witty one-liners

 

[ajetrumpet]

Yeah but I bet you stay up real late thinking up all those witty one-liners

Yes I do. And along with those come the crazy-a** "workaround-like" solutions that I post on this site. Too bad for those people that actually take the advice.

I was trying to count the number of crazy solutions and responses that I've posted so far, but my 30,000 friends didn't have that many fingers.

 

[CraigDolphin]

I suppose the best way to do it would be by DeptCodeID. Maybe set it up in sections that are defined by the first letter of the DeptCode, but use the DeptCodeID as the criteria. So for example, DeptCodeIDs 1-36 all start with 'A', and if a user chooses a currently active DeptCode starting with 'A', it will find the next un-assigned DeptCode in that range. DeptCodes starting with 'B' would have IDs ranging from 37-63, etc.

Wait a second here. Autonumber ID's are almost always meaningless (your linenumbers are the only exception I can think of). Let's first stick to the real world decision making process before we translate that to code.

So, your dept codes are all two-digit text strings. You want to have the db look for the first available code with the same first letter as the 'used' one the user tried to enter. This will be decided on the basis of ascending alphabetical order of the second letter in the string?

Is that correct.

If so, then what should the db do if ALL dept codes for that first letter are taken? Move to the next (alphabetically speaking) first letter and try again?

What do you want the db to do if ever ALL the single-use dept codes are in use? Use on the multiple-use codes? If so, which one?

 

[Colin@Toyota]

Wait a second here. Autonumber ID's are almost always meaningless (your linenumbers are the only exception I can think of). Let's first stick to the real world decision making process before we translate that to code.

So, your dept codes are all two-digit text strings. You want to have the db look for the first available code with the same first letter as the 'used' one the user tried to enter. This will be decided on the basis of ascending alphabetical order of the second letter in the string?

Is that correct.

The second digit is not terribly important. If a user selects say AR, the bd can just scan the whole range, and if AB is the first available DeptCode, that would be ok.

If so, then what should the db do if ALL dept codes for that first letter are taken? Move to the next (alphabetically speaking) first letter and try again?

Yes.

What do you want the db to do if ever ALL the single-use dept codes are in use? Use on the multiple-use codes? If so, which one?

No, the multiple use ones are used for recoveries initiated in-house by our QA members.

We have yet to encounter a time when all the DeptCodes were in use.

I just had a thought... this is starting to look very similar to the process used to determine line number. Is there some congruency here? Is it possible to use the same logic to assign DeptCode as there is to assign LineNumber?

 

[CraigDolphin]

this is starting to look very similar to the process used to determine line number.

There are parallels in that we need to allow the db to follow a decision tree to its end and I'm trying to get you to spell out the logic you want implemented. But Linenumber Values are meaningful in regard to priority whereas these codes do not appear to be.

We have yet to encounter a time when all the DeptCodes were in use.

YET. And that excuse will count for nought on the day your db 'breaks' because you didn't code to cover all possible occurences.

However, in this case, I think the answer is to have the db inform the user that no dept codes are currently available and reject the user's original value.

However, there is an alternative to all of this that occurs to me now. Instead of responding to the users choice of codes and testing to see if it's available, why not simply limit the list of codes that they can choose from in the combo to only those single-use codes that are available 'now', and multi-use codes?

 

[Colin@Toyota]

However, there is an alternative to all of this that occurs to me now. Instead of responding to the users choice of codes and testing to see if it's available, why not simply limit the list of codes that they can choose from in the combo to only those single-use codes that are available 'now', and multi-use codes?

I was thinking that from the beginning, but I couldn't think of a way to account for the multiple use DeptCodes.

So the combo box would now have a recordsource based on a fairly simple SQL statement?

Something like:

SELECT tblDeptCodes.DeptCodeID, tblDeptCodes.DeptCode
WHERE tblDeptCodes.MultipleUse = -1 AND tblSETR.DeptCodeID >< tblDeptCodes.DeptCode

Hm. That doesn't look quite right... but I think I get the basic idea... Right?

 

[Colin@Toyota]

YET. And that excuse will count for nought on the day your db 'breaks' because you didn't code to cover all possible occurences.

However, in this case, I think the answer is to have the db inform the user that no dept codes are currently available and reject the user's original value.

We can include another MsgBox with a smartass explaination of why the db can't handle it if you like

I agree with the second sentence. Just inform the user that there are currently no open Dept Codes. If that happens, I guess we would have to create some new ones.

 

[CraigDolphin]

Here Colin. Check this out.

The user selects from a combo that only show dept codes that are either multi-use, or are single use but have not been used in any SETR with an expiration date later than today.

The result of the selection is placed into a hidden textbox bound to the dept id. A visible textbox shows you the actual dept code chosen for the SETR. (if you just used the combo bound to the field you'd see a null in the combo for setr's with previously assigned deptcodes that no longer show-up in the list).

Using this approach, the user cannot enter a single-use code that's already being used in a current SETR, and whichever one they want to pick from is up to them. No significant code involved.

 

[Colin@Toyota]

That's pretty slick. Thanks!

Now, for adding VDSs... I understand now that the new ModelID will have to be created before any VDSs can be applied. So can I basically follow the same format as what you've done for the adding parts to models?

 

[CraigDolphin]

Yep...much the same thing. You'll need to add another form/subform pair to the db bound to the model/ModelVDS tables respectively. You already have a form for adding new models to the db. You may need to add a form to add new VDS codes (or use the notinlist event).