Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 38 of 38

Thread: User changes in a table/form

  1. #21
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Well done, sorry I wasn't around, but I have been busy.

  2. #22
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    My approach to the second problem has been included in the code below (which also includes the first solution).

    [vba]Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim myRecordChange As Recordset
    Dim myorderline As Variant
    Dim checkline As Variant
    Dim myconnection As ADODB.Connection
    Dim myDeletePull As New ADODB.Recordset
    Set myconnection = CurrentProject.Connection
    myDeletePull.ActiveConnection = myconnection
    myDeletePull.Open "TblOrderPull", , adOpenDynamic

    Set myRecordChange = Me.RecordsetClone
    myorderline = Me.saleorderlineid

    myRecordChange.FindFirst "saleorderlineid = " & myorderline
    If myRecordChange.NoMatch Then
    'MsgBox "record not found"
    Else
    'updates TblSaleOrderLine!saleprocessed from 1 (true) to 0 (false)
    myRecordChange.Edit
    myRecordChange.Fields("saleprocessed").Value = False
    Me.[saleprocessed].Value = False
    myRecordChange.Update

    'deletes any instances in TblOrderPull where salesorderline = myorderline
    myDeletePull.MoveFirst
    While Not myDeletePull.EOF

    checkline = myDeletePull.Fields("salesorderlineid").Value
    If checkline = myorderline Then
    MsgBox "Found at id: " & myDeletePull.Fields("pullid").Value
    myDeletePull.Delete

    Else
    End If

    myDeletePull.MoveNext

    Wend
    End If

    myDeletePull.Close
    myRecordChange.Close
    Set myDeletePull = Nothing
    Set myRecordChange = Nothing
    End Sub[/vba]

    The line [vba]myDeletePull.Delete[/vba] needs to delete the current row of data. I thought that it would work, but I get a Run-time error "3251": Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

    So what's the solution I need here, in order to delete the current record?

    Ah got the problem.

    [VBA]myDeletePull.Open "TblOrderPull", , adOpenKeyset, adLockOptimistic[/VBA]

  3. #23
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Is it "Solved" now?

  4. #24
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    Almost, which is why I haven't changed the status yet. (That way I can still add posts)

  5. #25
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    For example:

    I now want to add some buttons on the main form, that control the subform; such as GoToRecord.
    I've added buttons that do the following:
    DoCmd.GoToRecord , "FrmConsignee", acNext
    DoCmd.GoToRecord , "FrmConsignee", acPrevious

    with "FrmConsginee" being the name of the subform.
    The buttons occur on the main form.
    However when I press the buttons, I get the following error:
    "The object "FrmConsignee" isn't open."

    I've also tried using "Me.FrmConsignee" again without luck.

  6. #26
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Try it like this with your Form and field names

    Me![WorkHardware Subform].SetFocus

    Me![WorkHardware Subform].Form![Combo6].SetFocus

    DoCmd.GoToRecord , , acNext

  7. #27
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    Not sure I get what you mean.

    The cosde I currently have (which doesn't work) is:
    [VBA]Private Sub bttn_gotofirstdelivery_Click()
    On Error GoTo Err_bttn_gotofirstdelivery_Click

    DoCmd.GoToRecord , "Me.FrmConsignee", acFirst
    Exit_bttn_gotofirstdelivery_Click:
    Exit Sub
    Err_bttn_gotofirstdelivery_Click:
    MsgBox Err.Description
    Resume Exit_bttn_gotofirstdelivery_Click

    End Sub[/VBA]

    The parent form (Me) is called: FrmSupplier
    The subform is called: FrmConsignee
    The buttton to call the above code is called: bttn_gotofirstdelivery
    The above code is on the parent forms code.


    In your code below, what is Combo6 ?

  8. #28
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Combo6 is the first field on my Subform, what the code does is set the focus to the subform and then to the first field on that subform and THEN does the docmd.gotorecord.

  9. #29
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    Gotcha, thanks.

    Is it possible to create a form that the user can use to perform an advanced filter on the results of another form?

    For example, when the user goes from the Main Menu, into the "Edit Records", they are first directed to a form, which includes various fields (I guess they should be dropdowns - and a calendar). If the user completes any of the dropdowns, or selects a date on the calendar then the records are sorted accordingly.

    I'm certain this can be done, but having written the above, it seems more complicated than I initially thought.

    Suggestions please?

  10. #30
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes of course, you can do it by Query, Form Filter or by VBA generated SQL recordset.
    Take your pick.

  11. #31
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    I guess the question should be which is the most effective, and requires the least Mb?

    Can you help me with an example (of the most effective method)?

  12. #32
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I would suggest the Form Filter method provides a flexible system as the records to be viewed/edited casn be on a subform on the "filtering" form.
    Take a look at this example called Search 2000 that I posted on this thread.
    http://www.vbaexpress.com/forum/showthread.php?t=25027

  13. #33
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    Ecellent, the Allen Browne database seems very helpful, and I like the fact I don't need an extra form, but instead that I can put them directly on the form.

    BUT that does lead to another question.
    I use the same form, regardless of whether I am adding a new record, or editing an old record.

    However, I only want the search criteria to appear on existing records (the ones I can edit)- but not to appear if I am adding a new record (as that could confuse the end user).
    How can I do this? How can I tell the form not to show the search criteria if it's showing the new record?

    Thanks as always

  14. #34
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You use VBA code in the On Current event to set those fields & Combos to Visible or Invisible. It goes like this

    If Not me.newrecord then
    me.combonumberorname.Visible = True
    me.fieldname.Visible = True 'etc
    else
    me.combonumberorname.Visible = False
    me.fieldname.Visible = False 'etc
    End if

    Where combonumberorname are the actual names of your combos and fieldname are the names of your fields, if you have any.

  15. #35
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    Great thanks,
    That's a big help.
    I'm on holiday until 4th Jan now, so I'll be keeping this thread as unsolved until I'm sure I'm done asking questions about it.
    Happy Holidays!

  16. #36
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    I've got another question about the filter. (I know, I should be on holiday!)

    If the filter doesn't return any results, it just shows the new record. (And because of the way I've programmed it, it then won't show the filter options, so I can't then clear the irrelevant filter.

    Is there some smart piece of code I can use so that if the filter is run, and no results are shown, it just posts a message stating that no results were found, and then the filter is reset.

    (I'm still using Allen Browne's filter system)

    Thanks, and again, happy holidays.

  17. #37
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You can check the "Current" record's key field and if it null use a msgbox to tell the user and then set Filter off.

  18. #38
    VBAX Mentor
    Joined
    Nov 2008
    Posts
    305
    Location
    What code would I use to NOT print the first page of a report?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •