Welcome to SPN

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

Sign Up Now!

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...


Share This Page