Subform select qry and then update 1

[FurryGorilla]

H

This is getting a little frustrating now as I haven't done any Access for a while and I can't for the life of me remember how to do it. Basically I have two tables as follows:

tblComplaint(CCNo, Customer, SMNNo, DateRaised Area)
tblContainment(CCNo, Action, DateAgreed, Other)

Once the complaint has been entered into the tblComplaint the user can add containment action.

I have a form which has an unbound drop down box which has those CCNos from the tblComplaint table. When a CCNo has been selected the subform is updated by running a query on the tblContainment table using the cboCCNo from the main form. Any values that are returned by the query are inserted into the subform.

The problem I have is that when I insert/modify data in the subform I cannot run an add/update query from the main form as it is returning NULL values. I have a feeling it may be something to do with the select query used on the subform but I really haven't got a clue.

If anyone has a solution to this I would be grateful.

Thanks in advance
Chris


the user should be able to select the CC number on a form (SELECT qry to read into drop down box - unbound) and this will then query the tblContainment table and read it into a subform. The user should then be able to enter or odify the data on the subform and then press a button which will update the tblContainment table with the new/modified data.

Unfortunately I'm finding that I am unable to use t

 

[LittleSmudge]

The way you describe it seems to be going the hard way around a very simple problem.

I think you have one fundimental problem with your data structure, if I interpret your info correctly.
tblComplaint(CCNo, Customer, SMNNo, DateRaised Area)
tblContainment(CCNo, Action, DateAgreed, Other)

If CCNo is the Primary Key in tblComplaint ( I assume that's what the underline is for ) and you can allow many Containment actions for each Compaint the you cannot use CCNo as the primary Key in tblContainment

You need
tblComplaint(CCNo, Customer, SMNNo, DateRaised Area)
tblContainment(ContainNo,CCNoRef, Action, DateAgreed, Other)

ContainNo being the Primary Key in the tblContainment.

Have the subForm bound to the tblContainment. ( Any changes to the fields in the subForm are thus automatically stored in the tblContainment when the record changes focus. No 'button' or anything fancy needed )

the combo of CCNos is just a lookup on the main form to help you get to the correct tblComplaint record.

So in the combo box AfterUpdate event:-

Private Sub frmMain_AfterUpdate()
CCNoControlOnMainForm.SetFocus
DoCmd.FindRecord (cboPickTheCCNo)
End Sub

In the subForm control make the
LinkMasterFields parameter = CCNoControlOnMainForm
LinkChildFields parameter = ControlNameOnSubformContainingCCNoValue

Then, when you select a Complaint from the combo the main form will go to the correct record and the subform will ONLY show tblContainment record where the CCNoRef field matches the main forms CCNo

Also, as an added bonus Access will AUTOMATICALLY fill in the CCNoRef value for you if your user forces the subForm to a new record ( Creates a new Containment ) - thus keeping track for you all the time.

QED ?


G LS

 

[WildHare]

It sounds like the CCNO value you have is the one from the COMPLAINT table, not the CONTAINMENT table (thus the NULL value for the Containment CCNO). If your tables are INNER joined, your select query for the subform should utilize fields from the Containment table only. Then you should be able to update and add with carefree abandon....

Jim There are two ways to argue with a woman - neither one works.
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[FurryGorilla]

Hmm, maybe I should have read what I wrote and not have left out some information

OK, I understand what you're getting at with creating a primary key in the tblContainment table. The thing is this is going to be a 1 to 1 relationship The reason it has been broken down into two tables is so that the user can fill in small sections of the complaints at a time and then modify them as well.

Currently the drop own box (holding the CCNo from the tblComplaint table) requeries the form which holds the information from the tblContainment table using the following query:

SELECT tblContainment.CCNo, tblContainment.Action, tblContainment.DateAgreed,  tblContainment.Other
FROM tblContainment
WHERE tblContainment.CCNo=[Forms]![frmContainment]![cboCCNo]

LittleSmudge I've tried adapting your AfterUpdate event but end up with a macro errors.

cboCCNo.SetFocus
DoCmd.FindRecord (cboCCo)

-> 2162 error

cboCCNo.SetFocus
DoCmd.FindRecord (cboCCo.value)

-> 2042 error

Is there not a way of extracting the data from the subform text boxes and then using them in a query when the button is pressed to update the containment table?

Thanks for the replies
Chris

 

[FurryGorilla]

Oops should have read

cboCCNo.SetFocus
DoCmd.FindRecord (cboCCNo)

-> 2162 error

cboCCNo.SetFocus
DoCmd.FindRecord (cboCCNo.value)

-> 2042 error

Instead of having the subform bound to the select query is it possible to insert the values from running the query into the subform using an expression. Whenever I use the follwing:

Default Value:

= [qSelContainment]![Action]

where qSelContainment is the name of the query to return the containment data I end up with #Name? in the txtAction text box.

Ta
Chris

 

[LittleSmudge]

If it is a one-one then based on what you've said I see on reason why it souldn't be in a simgle table. You can still present the user with selected bits of the data in different forms to allow them to 'update' it in stages, but making one-to-one links seems like even more hard work.




LittleSmudge I've tried adapting your AfterUpdate event but end up with a macro errors.

cboCCNo.SetFocus
DoCmd.FindRecord (cboCCo) -> 2162 error

cboCCNo.SetFocus
DoCmd.FindRecord (cboCCo.value) -> 2042 error


If the combo box is called cboCCNo then you need to refer to it as cboCCNo You've missed out the N in the name

( Also I got the Sub name wrong - oops You should have the code in the cboCCNo_AfterUpdate event and refer to a control on the main form - NOT to the combo itself

Private Sub cboCCNo_AfterUpdate()
txtCCNo.SetFocus ' Set focus to a control on the main form that is BOUND to the CCNo field
DoCmd.FindRecord cboCCNo


Your approach using
SELECT tblContainment.CCNo, tblContainment.Action, tblContainment.DateAgreed, tblContainment.Other
FROM tblContainment
WHERE tblContainment.CCNo=[Forms]![frmContainment]![cboCCNo]
Will also work - But you could shorten it

"SELECT * FROM tblContainment WHERE tblContainment.CCNo = " & cboCCNo

Remember to keep the cboCCNo outside of the quotes so that it evaluared as a variable rather than a literal.


G LS

 

[FurryGorilla]

Thanks for the replies. I've made it much simpler and the person who's using it is happy with it.

Thanks again for the help
Chris