Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!
  1.   Become a Supporter    ::   Make a Contribution   
    Target (Recurring Monthly): $200 :: Achieved: $98

RE: Move feild entries from form to form using global variables

Discussion in 'Information Technology' started by JackCGW, Nov 14, 2005.

  1. JackCGW

    JackCGW
    Expand Collapse
    Guest

    Gunny,
    Please take a look at my post "Adding Invrentory Managment" dated today.

    By the way, the help you have given me so far has worked out spectacularly.
    I was struggling with the nz function , but all is well now.

    Is Gunny a GI nickname?

    Thanks
    Jack

    "'69 Camaro" wrote:

    > Hi, Jack.
    >
    > > DoCmd.GoToRecord acDataForm, "subformMaterialDetails", acNewRec

    >
    > I'm not pulling your leg. Whenever the main form is open, it _only_ has
    > controls. There's no subform open as if it were a real form. GoToRecord
    > must use the main form's name in this line of code, because it's _the_form_.
    > Unless you have a main form that's configured to bypass Access 2003's bug
    > with bound subforms, this command won't work for a subform control.
    >
    > My advice to you is to forget the subform idea and just apply the code to a
    > regular form. It's possible to go in through the back door to insert a new
    > record for the subform, but that requires more advanced coding skills and
    > knowledge of the form structure and the underlying table structures. I can
    > only guess on the structures, but I'll give you an example and let you decide
    > whether you want to take the easier route by skipping the subform idea.
    >
    > Private Sub CloseBtn_Click()
    >
    > On Error GoTo ErrHandler
    >
    > CurrentDb().Execute "INSERT INTO tblMyTable (MaterialDescription, " & _
    > MaterialUnitPrice, MaterialSalesTax, ID) " & _
    > "VALUES ('" Me!lstMaterialAddSelect.Column(1) & "', " & _
    > CCur(Me!lstMaterialAddSelect.Column(2)) & ", " & _
    > CCur(Me!lstMaterialAddSelect.Column(3)) & ", " & _
    > Forms!EditProject!txtID.Value & ")", dbFailOnError
    > Forms("EditProject")!subformMaterialDetails.Requery
    > DoCmd.Close acForm, Me.Name
    >
    > Exit Sub
    >
    > ErrHandler:
    >
    > MsgBox "Error in CloseBtn_Click( ) in" & vbCrLf & _
    > Me.Name & " form." & vbCrLf & vbCrLf & _
    > "Error #" & Err.Number & vbCrLf & Err.Description
    > Err.Clear
    >
    > End Sub
    >
    > The code above makes a lot of assumptions. It assumes:
    >
    > 1.) The subform is bound to the tblMyTable table.
    > 2.) The three fields that need values inserted from the selection in the
    > list box are named MaterialDescription, MaterialUnitPrice, MaterialSalesTax,
    > and that MaterialDescription is a Text data type and MaterialUnitPrice and
    > MaterialSalesTax are both Currency data types.
    > 3.) That the main form is named EditProject.
    > 4.) That the main form is bound.
    > 5.) That the subform control is named subformMaterialDetails.
    > 6.) That the subform is bound.
    > 7.) That there's a relationship between the table that the main form is
    > bound to and the table that the subform is bound to, and that relationship
    > requires that the ID field be the primary key on the one side (displayed in
    > the main form) and is the foreign key on the many side (but it doesn't need
    > to have the same name on the many side).
    > 8.) That the main form's LinkMasterField Property is the ID field.
    > 9.) That the main form's text box which displays the ID field is named txtID.
    > 10.) That the ID field is numerical.
    > 11.) That there are no required fields in the tblMyTable that don't have
    > default values. In other words, the values passed from the list box and the
    > foreign key satisfy all needs for a new record to be created.
    >
    > If any of these assumptions are wrong, you'll need to make adjustments to
    > the names, or the SQL syntax (for different data types), or compensate for
    > any additional required fields, or compensate for a different form structure,
    > or a different relationship.
    >
    > > We are almost there...I can feel it.

    >
    > If all you have to do is replace the names, then yes, we are.
    >
    > HTH.
    > Gunny
    >
    > See http://www.QBuilt.com for all your database needs.
    > See http://www.Access.QBuilt.com for Microsoft Access tips.
    >
    > (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    > be forwarded to me.)
    > - - -
    > If my answer has helped you, please sign in and answer yes to the question
    > "Did this post answer your question?" at the bottom of the message, which
    > adds your question and the answers to the database of answers. Remember that
    > questions answered the quickest are often from those who have a history of
    > rewarding the contributors who have taken the time to answer questions
    > correctly.
    >
    >
    > "JackCGW" wrote:
    >
    > > Gunny,
    > > I made the changes with high hopes...but...I was still getting the same
    > > error mesasge so I put a break point at the On Error line.
    > >
    > > Private Sub cmdAddSelectedItem_Click()
    > >
    > > On Error GoTo Err_cmdAddSelectedItem_Click
    > >
    > > DoCmd.GoToRecord acDataForm, "subformMaterialDetails", acNewRec
    > >
    > > Forms("EditProject").Controls("subformMaterialDetails")!MaterialDescription.Value = Me!lstMaterialAddSelect.Column(1)
    > >
    > > Forms("EditProject").Controls("subformMaterialDetails")!MaterialUnitPrice.Value = CCur(Me!lstMaterialAddSelect.Column(2))
    > >
    > > Forms("EditProject").Controls("subformMaterialDetails")!MaterialSalesTax.Value = CCur(Me!lstMaterialAddSelect.Column(3))
    > > DoCmd.Close acForm, Me.Name
    > >
    > > Exit Sub
    > >
    > > Err_cmdAddSelectedItem_Click:
    > > MsgBox "Error in cmdAddSelectedItem_Click() in" & vbCrLf & Me.Name & "
    > > form." & vbCrLf & vbCrLf & "Error #" & Err.Number & vbCrLf & Err.Description
    > > Err.Clear
    > >
    > > End Sub
    > >
    > > Now when I 'Step Into' the code, the error occurs at the DoCmd...AcNewRec
    > > line.
    > > I thought I could apply the same logic of calling to the control(subform)
    > > but I appearently don't know the proper "things" to type.
    > >
    > > Just to simplify these threads...
    > > The parent form is called EditProject
    > > The subform as a control is called subformMaterialDetails
    > > The lsitbox form is called MaterialAddSelectMiniform
    > > The cmdButton that gets all the clicking is on MaterialAddSelectMiniform and
    > > is called cmdAddSelectedItem
    > >
    > > Thanks for the help; We are almost there...I can feel it.
    > > Jack
    > >
    > >
    > > "'69 Camaro" wrote:
    > >
    > > > Hi, Jack.
    > > >
    > > > Open the main form in Design View. Open the Properties dialog window by
    > > > selecting the "Properties" button on the built-in toolbar. On the main form,
    > > > select the subform control so that there are "boxes" around its perimeter,
    > > > indicating "Selected Control." The Title Bar on the Properties dialog window
    > > > changes to "Subform/Subreport: MyCtrlName." Select the "Other" tab. Check
    > > > the Name Property, and it will match the same name as the one I've used as
    > > > MyCtrlName as a placeholder in the Title Bar. Replace my names with your
    > > > names in the following example (watch out for word wrap):
    > > >
    > > > Forms("frmBOM").Controls("MyCtrlName")!txtItem.Value = Me!lstItems.Column(1)
    > > > Forms("frmBOM").Controls("MyCtrlName")!txtDesc.Value = Me!lstItems.Column(2)
    > > > Forms("frmBOM").Controls("MyCtrlName")!txtCost.Value =
    > > > CCur(Me!lstItems.Column(3))
    > > >
    > > > . . . where frmBOM is the name of the main form, MyCtrlName is the name of
    > > > the
    > > > subform control, txtItem is the text box on the subform displaying the
    > > > item's name, txtDesc is the text box on the subform displaying the item's
    > > > description, and txtCost is the text box on the subform displaying the item's
    > > > cost. Save and compile the module again.
    > > >
    > > > HTH.
    > > > Gunny
    > > >
    > > > See http://www.QBuilt.com for all your database needs.
    > > > See http://www.Access.QBuilt.com for Microsoft Access tips.
    > > >
    > > > (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    > > > be forwarded to me.)
    > > > - - -
    > > > If my answer has helped you, please sign in and answer yes to the question
    > > > "Did this post answer your question?" at the bottom of the message, which
    > > > adds your question and the answers to the database of answers. Remember that
    > > > questions answered the quickest are often from those who have a history of
    > > > rewarding the contributors who have taken the time to answer questions
    > > > correctly.
    > > >
    > > >
    > > > "JackCGW" wrote:
    > > >
    > > > > Gunny,
    > > > > I actually understand that...but...I can't seem to find the subforms control
    > > > > name. I have looked all through the help files but no luck(usually isn't).
    > > > >
    > > > > Jack
    > > > >
    > > > > "'69 Camaro" wrote:
    > > > >
    > > > > > Hi, Jack.
    > > > > >
    > > > > > > As a stand alone form
    > > > > > > the code works flawlessly. But if I use the form as a subform
    > > > > >
    > > > > > I did mention that it's a simple example. Add subforms and you've got
    > > > > > complexity that you need to add code for.
    > > > > >
    > > > > > > The object 'subformMaterialdetails' isn't open.
    > > > > >
    > > > > > It isn't. It's now an object that the subform control is "holding" whenever
    > > > > > the main form is open. You must refer to this subform control, not the form.
    > > > > >
    > > > > > Change these three lines of code:
    > > > > >
    > > > > > Forms("frmBOM")!txtItem.Value = Me!lstItems.Column(1)
    > > > > > Forms("frmBOM")!txtDesc.Value = Me!lstItems.Column(2)
    > > > > > Forms("frmBOM")!txtCost.Value = CCur(Me!lstItems.Column(3))
    > > > > >
    > > > > > . . . to the following (and watch out for word wrap):
    > > > > >
    > > > > > Forms("frmBOM").Controls("MyCtrl")!txtItem.Value = Me!lstItems.Column(1)
    > > > > > Forms("frmBOM").Controls("MyCtrl")!txtDesc.Value = Me!lstItems.Column(2)
    > > > > > Forms("frmBOM").Controls("MyCtrl")!txtCost.Value =
    > > > > > CCur(Me!lstItems.Column(3))
    > > > > >
    > > > > > . . . where frmBOM is the name of the main form, MyCtrl is the name of the
    > > > > > subform control (_not_ the subform's name), txtItem is the text box on the
    > > > > > subform displaying the item's name, txtDesc is the text box on the subform
    > > > > > displaying the item's description, and txtCost is the text box on the subform
    > > > > > displaying the item's cost. Save and compile the module again.
    > > > > >
    > > > > > HTH.
    > > > > > Gunny
    > > > > >
    > > > > > See http://www.QBuilt.com for all your database needs.
    > > > > > See http://www.Access.QBuilt.com for Microsoft Access tips.
    > > > > >
    > > > > > (Please remove ZERO_SPAM from my reply E-mail address so that a message will
    > > > > > be forwarded to me.)
    > > > > > - - -
    > > > > > If my answer has helped you, please sign in and answer yes to the question
    > > > > > "Did this post answer your question?" at the bottom of the message, which
    > > > > > adds your question and the answers to the database of answers. Remember that
    > > > > > questions answered the quickest are often from those who have a history of
    > > > > > rewarding the contributors who have taken the time to answer questions
    > > > > > correctly.
    > > > > >
    > > > > >
    > > > > > "JackCGW" wrote:
    > > > > >
    > > > > > > Well Gunny,
    > > > > > > That worked; albeit I have no idea how...but that;s why I am a cabinet maker
    > > > > > > and you are a programmer.
    > > > > > > Now when I say worked I realy mean sort of worked. As a stand alone form
    > > > > > > the code works flawlessly. But if I use the form as a subform linked to the
    > > > > > > mother by ProjectID, I get an error that states:
    > > > > > >
    > > > > > > Error #2489
    > > > > > > The object 'subformMaterialdetails' isn't open.
    > > > > > >
    > > > > > > How is that possible?
    > > > > > >
    > > > > > > Jack
    > > > > > >
    > > > > > > "'69 Camaro" wrote:
    > > > > > >
    > > > > > > > Hi, Jack.
    > > > > > > >
    > > > > > > > As per your request in your other thread, the easiest way to accomplish this
    > > > > > > > task is to let the list box automate the process. I'll give you a simple
    > > > > > > > example, but you'll need to replace the names in the example with your own.
    > > > > > > > First, create a new query and paste the following in the SQL View pane:
    > > > > > > >
    > > > > > > > SELECT ID, ItemName, ItemDesc, Cost
    > > > > > > > FROM tblItems
    > > > > > > > ORDER BY ItemName;
    > > > > > > >
    > > > > > > > . . . where ID is the primary key of the table listing the items, ItemName
    > > > > > > > is the item, ItemDesc is the item's description, Cost is the item's cost, and
    > > > > > > > tblItems is the name of the table.
    > > > > > > >
    > > > > > > > Save the query and name it qryItems.
    > > > > > > >
    > > > > > > > Open the form that contains the list box in Design View and open the
    > > > > > > > Properties dialog window. Select the list box, and then select the Data tab,
    > > > > > > > and then select the "Row Source" Property. Scroll down and select qryItems.
    > > > > > > > Select the Format tab, and then select the "Column Count" Property. Type 4
    > > > > > > > in the text box. Select the "Column Widths" Property and type 0";1";0";0" in
    > > > > > > > the text box. This way, only the item's name will be displayed.
    > > > > > > >
    > > > > > > > In the form's module in the list box's OnAfterUpdate( ) event, paste the
    > > > > > > > following:
    > > > > > > >
    > > > > > > > Private Sub lstItems_AfterUpdate()
    > > > > > > >
    > > > > > > > On Error GoTo ErrHandler
    > > > > > > >
    > > > > > > > Me!txtItemDesc.Value = Me!lstItems.Column(2)
    > > > > > > > Me!txtItemCost.Value = CCur(Me!lstItems.Column(3))
    > > > > > > >
    > > > > > > > Exit Sub
    > > > > > > >
    > > > > > > > ErrHandler:
    > > > > > > >
    > > > > > > > MsgBox "Error in lstItems_AfterUpdate( ) in" & vbCrLf & _
    > > > > > > > Me.Name & " form." & vbCrLf & vbCrLf & _
    > > > > > > > "Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
    > > > > > > > Err.Clear
    > > > > > > >
    > > > > > > > End Sub
    > > > > > > >
    > > > > > > > . . . where lstItems is the name of the list box, txtItemDesc is the text
    > > > > > > > box displaying the description, and txtItemCost is the text box displaying
     
  2. Loading...


Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page