Lots of questions on forms, functionality 1

[shellig]

Ok, to be humorous, I will say I made a database for lemonade.
I have a table for a pitcher of lemonade, tblPitcher and one for the glasses from the pitcher tblGlasses and a junction table tlnkPitGls. I am using the junction table to ensure that no glasses can be chosen that have not been made in pitchers…(lookup)
There are expected to be two glasses per pitcher of lemonade.
The pitchers are numbered 02-001-002 then 02-003-004, etc. (02 is for 2002)
The glasses are to be numbered 02-001, 02-002, etc.

1. I do not want the glasses to be poured and distributed until all of the information on the pitcher is complete. I am not sure how to ensure that
1.1. Maybe a button on the bottom of the pitcher form to open the glasses form that will only work if the data is complete, otherwise an error message comes up…How do I do that?
1.2. If I can do that, can I close the pitcher form first and go to the first correct record for the glasses?

2. I would also like the lookup for the tblGlasses not to include glasses that have already been poured…is this possible?

3. Once the glasses have been poured and distributed and all of the data is in place for the glasses, I do not want the records to be able to be changed. How could I do this?
3.1. How about a message popping up if a yes/no “Close Record” field is selected (where the field is automatically populated once all the data is input?) How would you suggest I do this?

4. If lemonade is spilled out of the pitcher or a glass is dropped, a glass is “scrapped.” How should I create a scrap record?
4.1. Should I have a separate table for scrap where I can put the reason (since a glass can be scrapped out of the pitcher or the glass)?
4.2. Is a yes/no field on each form (frmPitcher and frmGlasses) for scrap and a pop-up form where the scrap reason can be input reasonable?
4.3. If I have lookup fields for scrap reasons, can I have scrap reasons that only show up if the scrap comes from frmPitcher and different ones for frmGlasses?

I know this is a lot and I have a lot to learn but I am hoping that the answers you give me will help me out with my real-life process control database.
Thanks for your help,
Shelli Godfrey

 

[SBendBuckeye]

A couple questions, are the two GlassIDs combined to make up the PitcherID (eg Glasses 02-001 and 02-002 make up Pitcher 02-001-002)? If so, and a glass is scrapped, say 02-009, does that mean that the next PitcherID after 02-007-008 is 02-010-011 or is a replacement Glass 02-009 created so the PitcherID is still 02-009-010?

Also, the way you described it, I don't see how you will have a one to many relationship between glasses and pictures for your junction table.

I'm not sure where you are going with this, but here are a couple ideas. If there is no necessary connection between a given glass and its pitcher, then put a complete flag in the pitcher table. When it is finally true, then assign the next two glasses to that pitcher ID. If one is scrapped out then get the next glass.

When you create the foreign key in the Glass table by entering the Pitcher ID into it, the presense of a not null pitcher ID tells you that it is no longer available. You could also use an explicit flag for filled as well as one for scrap. A scrap date could also be used as an implicit flag for unavailable glasses.

Once a glass is filled or scrapped then it would no longer be available or changeable. I don't know if I'm helping you or not, but it has been interesting trying to think this through with you.

Perhaps a little more detail on exactly what you are trying to accomplish would help.

 

[shellig]

I completely forgot where I had posted these questions!

In answer to your question, the problem is that the pitcher is named first and the subsequent glasses poured regardless of whether they are scrapped or not have portions of the pitcher name as their name (for traceability), e.g. the example you gave, Glasses 02-001 and 02-002 come from Pitcher 02-001-002.

I have a table with information about what goes into the pitcher and another table with information about who drinks the glasses, quality, etc. The junction table is to join the two tables so I can easily make correlations.

I would like the junction table automatically filled out once the Pitcher is made with the two possible glass numbers for a lookup table on the glass form.
I would also like only the glass names that have not been used to show up in the lookup and don't know how to do that...that sounds kind of difficult!

This is really important when you are changing the ingredients and ratios in the pitcher and trying to get information on what people like! VERY similar to what I am doing with the product I am making and easy to relate to without explaining what I am making!

Also, I am not sure how to handle the scrap information. Would you make a separate table or just a key for scrap and query scrap information based on the key...this one is more complex because the scrap I need to document in reality is more than just dropping a glass of lemonade!!
Hope y'all can help me with some tips,
Shelli

 

[shellig]

More info on this problem at thread702-284600 if that is helpful!
Thanks again, Shelli

 

[FoxProProgrammer]

Hi, Shelli. I followed you over here from Thread702-284600. I'd really like to try to help but at this point I'm lost and don't understand what you are trying to do. Maybe it would help if you post some more details such as the structure of your tables, how they are related, and what you are trying to do with the forms. The information that you have provided might seem detailed to you, but it is very vague to someone that hasn't seen your database. If you provide more info, I'm sure that someone will be able to help and I'll give it a try too.

Best,
dz
dzaccess@yahoo.com

 

[mjones219]

I think that I have a similar issue and posted it in another forum -- Here is my post -
mjones219 (TechnicalUser) Jul 18, 2002
I have an Assignments Table (tblAssignment) that I need to have incremental numbering on a field so that each record has a unique number. However the numbering is alpha-numeric:
YYYY-xxx-0000
where YYYY = the current year (4-digits);
and xxx is 3-alpha-characters limited to a select list;
and 0000 is the incremental number up to 9999 (we will never have more than 9999 of any of these based upon the alpha characters)

For example: 2002-COI-0145; 2002-COI-0146; 2002-PAI-0145
I would like to have the 3-alpha-characters be selected from a combo box (?) limited to the list.

?Is this feasible?, and if so how?
?Also, can I use this as my primary key?, and if so how should I set this up?

Thanks.

And here is a followup question -
I have a table tblEntityBus and want to relate tblEntityPer using a similar numbering as mentioned above:
2002-0001-0002, 2002-0001-0003, 2002-1911-0002, 2002-1911-0003, etc.
However, I want the prefix to be the current year, the middle series to be selected and the suffix sequence to be sequential autonumbering (last number for the middle series +1). In other words the '2002' is the year, the '1911' corresponds to the Business Entity autonumber in tblEntityBus and the '0002' to be the next available number for a new Person in tblEntityPer related to the Business Entity.
?AM I totally confusing, confused or what??

 

[b31luv]

Hey Shellig,

I was reading through the post as I do everyday in case I find something useful. After reading your post I would like to say that every question you asked, from what I know and have learned, can be done. I think you maybe able to get answers to your questions if you simplify you question a little. Put together a form, try to make it work, and then ask a question concerning that. Then work on another portion. I just learned a lot about Dbases in the past few months by doing just that. Your question reads as if you want someone to sit back and put all this info together for you. This is a help form, not get your work done for free....Hope this helps. And like I stated, start by trying to put the forms together first, then the questions. That's how this forum worked for me and it can work for you too.

 

[shellig]

Just read this thread again and the last post was rude and inappropriate because I had been working on the project for months...and still almost a year later am not able to do what I wanted.

The comment I am referring to said "Your question reads as if you want someone to sit back and put all this info together for you. This is a help form, not get your work done for free....Hope this helps."
Yeah really helpful!

 

[SBendBuckeye]

Hello Shellig,

I'm still a little bit confused about exactly what you are trying to do. What about something like this:

tblPitcher
PitcherID
RestOfFields

tblGlass
PitcherID
GlassID
RestOfFields

PitcherID is the primary key of tlbPitcher and you have a compound primary key of PitcherID and GlassID for tblGlass. If that doesn't work, post a couple examples of the type of data you are looking for and maybe we can help.

Going back to your original post, are you saying that you have something like the following where you have a premade list of pitchers and glasses and a pitcher record takes the next available 2 glasses? If the following scenario is not accurate, could you please clarify it for me?

1. Before anything happens

tblPitcher tblGlass tblJunction
001 001
002 002
003 003
004 004
005 005
006 006
007 007
008 008

2. tblJunction record one is created

tblPitcher 001
tblGlass 001
002

3. tblJunction record two is created

tblPitcher 002
tblGlass 003
004

4. tblGlass 005 breaks

5. tblJunction record three is created

tblPitcher 003
tblGlass 006
007

Good Luck!

 

[jfgambit]

Shelli,

I don’t think anyone intended to be rude. You had taken the time to write down all your issues and I believe the number of questions may have made it seem to some that you were starting a database rather than in the middle of one and trying to trouble shoot problems. Please do not be turned of to this open forum do to a misunderstanding. We have all and are in some cases still learning new aspects in regards to Access and at times it can seem cumbersome. With that I will remove myself from this proverbial soapbox and answer as many of your questions as possible.

1. I do not want the glasses to be poured and distributed until all of the information on the pitcher is complete. I am not sure how to ensure that
1.1. Maybe a button on the bottom of the pitcher form to open the glasses form that will only work if the data is complete, otherwise an error message comes up…How do I do that?

I would use the following to check all the textboxes in your form to confirm all have been filled in:

Private Sub Commandbutton_Click()
Dim ctl As Control
Dim num
num = 0
For Each ctl In Me.Form
If ctl.ControlType = acTextBox Then
If ctl = "" Or IsNull(ctl) Then
num = 1
Else
End If
End If
Next ctl
If num = 1 Then
MsgBox ("Not all information entered, please try again.&quot
Else
docmd.RunCommand acCmdSaveRecord
End If
End Sub

1.2. If I can do that, can I close the pitcher form first and go to the first correct record for the glasses?

Not sure what you are trying to accomplish here. Please be a little mor descriptive. Do you not want to save the record? Do you want to go to the Glasses that are associated with the pitchers?

2. I would also like the lookup for the tblGlasses not to include glasses that have already been poured…is this possible?

Add a completed poured Column to the Glasses table that is a Yes/No field. Then filer the data based on in this field equaling YES. You can do this via a Query linked to the form.

3. Once the glasses have been poured and distributed and all of the data is in place for the glasses, I do not want the records to be able to be changed. How could I do this?

Same as 1.1. above only on the On Open Event of the form:
Private Sub Form_Open()
Dim ctl As Control
Dim num
num = 0
For Each ctl In Me.Form
If ctl.ControlType = acTextBox Then
If is notnull(ctl) Then
num = 1
Else
End If
End If
Next ctl
If num = 1 Then
ctl.locked = True
Else
ctl.locked = False
End If
End Sub


3.1. How about a message popping up if a yes/no “Close Record” field is selected (where the field is automatically populated once all the data is input?) How would you suggest I do this?

Use the above instead

4. If lemonade is spilled out of the pitcher or a glass is dropped, a glass is “scrapped.” How should I create a scrap record?

Please be a little more descriptive as to what this is for. If the records are locked or required via the above then how could they be scrapped? Are you saying after all the above is completed?

Answer the above be fore we get into the next steps.

4.1. Should I have a separate table for scrap where I can put the reason (since a glass can be scrapped out of the pitcher or the glass)?
4.2. Is a yes/no field on each form (frmPitcher and frmGlasses) for scrap and a pop-up form where the scrap reason can be input reasonable?
4.3. If I have lookup fields for scrap reasons, can I have scrap reasons that only show up if the scrap comes from frmPitcher and different ones for frmGlasses?


"I know what you're t'inkin', ma petite. Dat Gambit... still de suave one, no?"

 

[shellig]

Thank you all for your help. I am getting a friend who is expert at this stuff to help me one on one. I appreciate all the positive feedback to my last post.