Full Version: Opening New Form Based On Multiple Values On Subform
UtterAccess Forums > Microsoft® Access > Access Forms
Csharp821
Guys, I've run into a problem on a database that I've been working on for work and I can't seem to find an answer to my problem on any forums. I've got a parent form (Splash) that has two subforms (AddNew) and (ChangeCust) embedded into it. I have a combo box on the form ChangeCust called DivName (text) and a textbox called CustNum (long). I set the OnClick property of a button on the ChangeCust form to select and append records from tbl_Master to tbl_ActiveCust and I'm trying to open up another form called EditExisting to the record in tbl_ActiveCust that has the values in Forms!Splash!ChangeCust!DivName and Forms!Splash!ChangeCust!CustNum. This is the first large scale database that I've ever created and I'm fumbling in the dark. I've attached a zipped copy of my DB to this posting. Please pardon my ignorance on Access/VBA terminology. Any help would be appreciated. I'm sure it's something simple, but I've been working on it since Friday and I can't figure it our and finally came to UA for help. Thanks
Jeff B.
Just based on the names of your form/subforms, I'm wondering why you're using subforms. The main form/subform construction in Access is designed to give you an easy way to display data that's in a Parent-Child (one-to-many) relationship. The "one" side data shows in the main form, the "many" side data shows in the subform.
It sounds, from your description (and the names of the forms), like you're using subforms to perform different functions (e.g., adding and editing).
More info, please...
Jeff B.
and by the way, welcome2UA.gif
Csharp821
Originally, I was going to use individual forms for each of the tasks, but my boss suggested that he wanted the functionality of adding new customers and editing existing customers on the form that would load at startup (Splash). I'm trying to teach myself VBA at the same time as form design which may be a mistake, so I want to store open the form EditExisting to the record in tbl_ActiveCust where the values in [Forms]![Splash]![ChangeCust]![Divname] and [Forms]![Splash]![ChangeCust]![CustNum] match. Is there a smarter way to do it. I've onlt spent about a week on it so far, so I'm not opposed to starting from scratch if I've been going about it wrong.
RJD
Hi: I add my welcome as well.
heck out my mod to your db. In the SQL in your Event Procedure you didn't fully qualify the subform. For example...
CODE
"FROM tbl_Master WHERE (((tbl_Master.DivName)=[Forms]![Splash]![ChangeCust].form![DivName]) AND " & _
"((tbl_Master.CustNum)=[Forms]![Splash]![ChangeCust].form![CustNum]))"

Note that the ".form" was added after the [ChangeCust]. I also tinkered with the syntax on calling for fields to Me!xxxx (bang instead of dot). Seems to work now, if I understand correctly.
Odidn't check the other subform procedure(s) ...
(I didn't check for opening the form to the record just made - sorry, just saw that. I'll look now.)
HTH
Joe
(edit: added comment)
Csharp821
Joe, thank you for the assist on the syntax. Whenever I click the button on the form ChangeCust, I need the form EditExisting to open using the values from CustChange.DivName and CustChange.CustNum, the division name (combo) and customer number (text) on the form ChangeCust. I may not be explaining what I'm trying to do correctly.
RJD
I think you covered that - and I just missed it (sorry - see the extra note above). I'll look at that again and see if I can fix that.
Joe
RJD
Check this revision. It filters the form on the fields in the Splash subform when it opens. But also adds a button to show all records if you need that.
HTH
Joe
Csharp821
Joe, that is exactly what I needed. Thank you so much for the help!! I'll keep that code for reference, but it actually makes sense now that I see it. Thank you!!
RJD
br />Glad that is what you needed. Let us know if you need more assistance. Someone's always here.
Regards,
Joe
Csharp821
drunk2.gif <== That was about to be my night. Thank you again!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.