Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    4

    Unanswered: Somewhat new to dbs, trouble with a small bit of VBA for Access.

    tl;dr: Where do I put my code so a macro for a button on the subform can find it, and how can I write go to new record for my subform? Something like

    Public Sub newComment()

    Forms!frmProjectTracker!frmCommentInputSubform.Set Focus
    DoCmd.GoToRecord , , acNewRec


    End Sub



    but something that works? Thanks ;(

    Hey all,

    Somewhat new to databases but I have a nicely normalised set of data and subform objects on my main form, which is fine. I'm trying to create a secure comment history, so that a user can input a comment, hit a button, and then it cannot be edited or deleted and it can be viewed via report. I almost have it done in several ways but I am foiled by one thing.

    Further explanation at the bottom, but in short, I'd like to know how I'd write a function that goes to a new record in a subform, so I can have a macro which:

    saves form
    goes to new record in subform (function)
    opens my report from the subform table

    I'd just like to know where I should write the code, and how to properly reference the subform control to go to a new record.

    Thank you so much for any help, I've gone about this in a lot of ways and nothing works as documented below ;(

    ---
    I tried the =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)) method that appears in the sample database 'Sales Pipeline' but it didn't work and I've heard it's not a great method - the functionality is somewhat what I'm after though.

    I tried making two tables, one which is linked to fields on the form and gets the input, then a button which runs a macro which appends the data to another table, then deletes it from the input table - so the input table only ever has one record and it's temporary. Then I can report from the history table I appended to. However, it doesn't work well as sometimes on input, the table doesn't take the values, it appends and deletes 0 rows and you have to hit the button again.

    What I thought was I could just embed a subform, remove the navigation buttons, and then have macro which goes to the next record and opens the report from that table - that way users have no way of backtracking and editing/deleting and it's somewhat secure (for a group of 5-8 non-tech savvy users).
    Last edited by jetimmins; 12-03-12 at 12:34.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'm going to take a guess that the reason your post has been viewed by over 100 members and no one has responded is that, like myself, they really have no idea of what you're trying to do here! Your post looks like you started to write it, stopped, then started all over again, with

    "Hey all..."

    The only part that really makes any sense, to me, is

    Quote Originally Posted by jetimmins View Post

    ...I'm trying to create a secure comment history, so that a user can input a comment, hit a button, and then it cannot be edited or deleted...
    If this is the core of your problem, it can be done as follows:

    In this hack a second, Unbound Textbox named (TempDataBox) is used to enter the data into. This Textbox is originally hidden, and when the Command Button named (IndirectDataInput) is clicked, it appears. Data is entered, and when the Command Button (now captioned with "Commit Data") is clicked again, the entered data is added to the Memo Field Control, named CommentsField in this demonstration.

    In this example, there are two Memo Field Textboxes, but only one is Bound, since the other one is simply a temporary holding area. The Bound Memo Field Textbox, named CommentsField, here, must have its Locked Property set to Yes/True, so that all data entry has to be done through the temporary Textbox.

    TempDataBox is Unbound, and in the Property Pane its Visible Property must be set originally set to 'No.' I place mine side by side with the CommentsField Textbox so that the user can refer to what's currently in the CommentsField Textbox while entering new notes.

    Once again, the CommentsField Textbox is Bound to the Form's underlying Table/Query, and its Locked Property is set to 'Yes.'

    Place a Command Button on the Form. Name it IndirectDataInput and in the Properties Pane set its Caption to "Add New Data"

    Now use this code:
    Code:
    Private Sub IndirectDataInput_Click()
    If IndirectDataInput.Caption = "Add New Data" Then
       TempDataBox.Visible = True
       TempDataBox.SetFocus
       IndirectDataInput.Caption = "Commit Data"
     
    Else
       IndirectDataInput.Caption = "Add New Data"
       If IsNull(Me.CommentsField) Then
          If Len(Me.TempDataBox) > 0 Then
            Me.CommentsField = Me.TempDataBox
            Me.TempDataBox = ""
            TempDataBox.Visible = False
          Else
            TempDataBox.Visible = False
          End If
        Else
          If Len(Me.TempDataBox) > 0 Then
           Me.CommentsField = Me.CommentsField & vbNewLine & Me.TempDataBox
           Me.TempDataBox = ""
           TempDataBox.Visible = False
          Else
           TempDataBox.Visible = False
          End If
          
        End If
    End If
    End Sub


    So, you click on the Command Button, the temporary Textbox appears, you enter your new data, click the Button again, the data is added to the Memo Field Textbox, and the temporary Textbox disappears.

    You'll have to replace CommentsField, in the code above, with the actual name of the Textbox on your Form that is Bound to your Memo Field.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2011
    Posts
    4
    Linq,

    Thanks so much for this. Sorry about the vagueness, as I said - pretty new to Access, I was hoping people would understand what I mean by 'Sales Pipeline' example db ;(

    I'll try this out now and hopefully all will go well.

    Thanks again.

  4. #4
    Join Date
    Nov 2011
    Posts
    4
    Linq,

    Worked like a charm. I had to change the Me. to Me! or it kept giving me problems, but apart from that it seems OK. I'll try to get this to work with a user name input and timestamp as well now.

    Thanks again.

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by jetimmins View Post

    ...I'll try to get this to work with a user name input and timestamp as well now...
    Here's the same code with a Time Stamp added; adapting it to include the user name should be simple:
    Code:
    Private Sub IndirectDataInput_Click()
    If IndirectDataInput.Caption = "Add New Data" Then
       TempDataBox.Visible = True
       TempDataBox.SetFocus
       IndirectDataInput.Caption = "Commit Data"
    Else
       IndirectDataInput.Caption = "Add New Data"
       If IsNull(Me.CommentsField) Then
          If Len(Me.TempDataBox) > 0 Then
            Me.CommentsField = Now() & "  " & Me.TempDataBox
            Me.TempDataBox = ""
            TempDataBox.Visible = False
          Else
            TempDataBox.Visible = False
          End If
        Else
          If Len(Me.TempDataBox) > 0 Then
           Me.CommentsField = Me.CommentsField & vbNewLine & Now() & "  " & Me.TempDataBox
           Me.TempDataBox = ""
           TempDataBox.Visible = False
     
          Else
           TempDataBox.Visible = False
          End If
          
        End If
    End If
    End Sub


    If you only want the date, rather than the date and time, simply replace Now() in the code with Date().

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Nov 2011
    Posts
    4
    Thanks again Linq, got the date and username going already =)

    The only other thing I could use help on is that the comments field that our bound text box is referring to is on the table which the main form is made from, and ideally I'd like it to link to another table which I can draw a relationship to that I already have set up. I don't know if that's possible or easy, if not, no worries, I can make do.

    Rather than having comments related to the project (main table form) they're related to the packages (multiple packages per project, so the history box would be linked to scrolling through the packages subform). I can probably just make a dropdown box for the input part and add that like user and date so that each comment will have an association with project if this isn't possible though.

    Thanks again.

Posting Permissions

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