Updating junction table with form/subform input?

[shellig]

Ok, to be humorous, I will say I made a database for lemonade.
I have a table tblPitcher and a one tblGlasses and a junction table tlnkPitGls.
I had created a form for the information about creating the pitcher of lemonade (tblPitcher) with a subform to create the records for the junction table.
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)
I want the glasses to be numbered 02-001, 02-002, etc.

When I enter the pitcher number, I would like the glasses to appear on the sub-form (2 records for each pitcher).
How would you recommend I do this?
I thought about doing some code on change to the pitcher number such that the subform has two records:
txtGls 1: left(txtPit,6)
txtGls 2: left(txtPit,3)+right(txtPit,3)
but I am not sure how to right the code to do it!
Thanks for your help,
Shelli Godfrey

 

[shellig]

Oops, I wrote right instead of write...I forgot where I posted this question. Does anybody have any ideas for me?
Shelli

 

[shellig]

OK,
I am getting somewhere but now I am crashing Access 97 with my little on Change (for Pitcher "lot&quot updating the subform.

I first want to say I am a real newbie and am picking up a lot of dribs and drabs through this board so PLEASE do not slam this attempt!

Private Sub txtPit_Change()
Me.tlnkPitGls_subform.SetFocus
Me.tlnkPitGls_subform.MoveFirst
If Me.tlnkPitGls_subform.Form.RecordsetClone.RecordCount > 0 Then
   Me.tlnkPitGls_subform.MoveFirst
    Me.tlnkPitGls_subform.Form.strExtLot = Left(txtPit, 6)
    If Me.tlnkPitGls_subform.Form.RecordsetClone.RecordCount = 1 Then
        DoCmd.GoToRecord , , acNewRec
        Me.tlnkPitGls_subform.Form.strExtLot = Left(txtPit, 3) + Right(txtPit, 3)
    Else
        Me.tlnkPitGls_subform.MoveNext
        Me.tlnkPitGls_subform.Form.strExtLot = Left(txtPit, 3) + Right(txtPit, 3)
    End If
End If

'Me.tlnkPitGls_subform.Requery
Me.tlnkPitGls_subform.Form.Repaint
End Sub

Please, please, please tell me what I don't need, what I might be writing incorrectly, etc. I have another posting regarding this problem that might explain what I am trying to do beter but I don't know how to point to it here.
Maybe this will work? thread702-284611

 

[FoxProProgrammer]

Hi Shelli,

I'm just curious why you are using a subform for this. Normally you use a subform when you want to display data from a one to many relationship. Even though each pitcher has two glasses, I don't see how you have a one-to-many relationship set up because of the way the data is stored. You are basically concatenating the year and two glass numbers together to create the pitcher number.

I think that you are on the right track with the Left and Right functions. Are you using a combo box or text box to enter the pitcher? You should be able to display the glass numbers in text boxes on the main form with the following:

txtGls 1 = Left(txtPit, 6)
txtGls 2 = Left(txtPit, 3) & Right(txtPit, 3)

You can reference text boxes on the subform like this:

Forms![MainFormName]!tlnkPitGls_subform.Form![txtGls 1] = Left(txtPit, 6)

Forms![MainFormName]!tlnkPitGls_subform.Form![txtGls 2] = Left(txtPit, 3) & Right(txtPit, 3)

Just replace MainFormName with the name of your main form and it should work. You don't even need to store the glass numbers in a table because the glass numbers can be derived from the pitcher number. I don't really understand what the code does in your previous post. If I misunderstood something, please post back and I'll try to help further. dz
dzaccess@yahoo.com

 

[shellig]

More info on this problem at thread702-284600 if that is helpful!
(Explains why I need the subform as it shows that the records are correct for the junction table.)
Thanks again, Shelli

 

[FoxProProgrammer]

Shelli,

Did you intend to link a different thread? You accidentally linked this thread (Thread702-284600).

If all you want to do is display the glass numbers, this problem doesn't seem all that complex. Like I noted in my last post, the glass numbers are actually concatenated in the pitcher number. You don't need to store the glass numbers in the table because their values can be derived from the pitcher number, and you can display the glass numbers on the subform using the syntax that I gave you. If you are using a combo box, all you need to do is assign the value of the two text boxes in the After Update event of the combo box as shown below.

Forms![MainFormName]!tlnkPitGls_subform.Form![txtGls 1] = Left(txtPit, 6)

Forms![MainFormName]!tlnkPitGls_subform.Form![txtGls 2] = Left(txtPit, 3) & Right(txtPit, 3)

I'll take a look at the other thread after you post the correct link.

Is this at all what you are looking for?

Best, dz
dzaccess@yahoo.com

 

[shellig]

Oops, thread702-284611 should be the right thread.
In short, I do not just want to display the glass numbers. I want them to automatically be entered into a junction table so I can link a table with the glass and pitcher information. This will allow me to evaluate, for example, customer satisfaction of the glass of lemonade versus the ingredients that went into that pitcher of lemonade and draw conclusions about how to make the best darn pitcher of lemonade for everyone...hopefully!
Thanks for all your help!