Results 1 to 5 of 5
  1. #1
    mbake085 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    21

    Continuous Form Disable Command Button

    Hey Everyone,



    I have continuous form bound to a table called [INVOICE_APPROVER]. This form serves as a rough draft for recording data on invoices. Once data is completed and invoices have been approved I have a button that appends the data to the final invoice table, where it is then deducted from the budget table and everything else.

    This works great, however I need to put some control on the button so entries are not duplicated on multiple button presses.

    Here is what I have tried so far:

    I have the INVOICE_ENTRY command in the continuous for run the query and disable using this code:

    Private Sub INVOICE_ENTRY_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "INVOICE_ENTRY"
    DoCmd.SetWarnings True
    Me.PR.SetFocus
    Me.INVOICE_ENTRY.Enabled = False
    Me.Repaint
    End Sub

    This works, but since a continuous form it disables all the buttons... can't have that.

    I then tried adding another step by having the INVOICE_ENTRY command open an unbound single form, setting default values to the original INVOICE_APPROVER form, and placing a ADD_ENTRY button in the Footer. I used the same code as above adapted for this new form. This works as well, however when I close the form and click the INVOICE_ENTRY command on the continuous for the ADD_ENTRY command is no longer disabled.

    I think I'm out of ideas, and I know this is a lot of work for something that could just be bound to the original invoice table, however doing it this way is somewhat necessary.

    Any help is appreciated.

    Thanks,

    Matt

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    I know you don't want the lecture; that this should be simply bound to the invoice table and then have an 'Approved' check box field - - or possibly an OptionGroup with Applied, Approved and other potential states. That's all it really takes and provides you the exact same ability to have correct data.....

    so getting off the db design soap box..... its not clear if your button is in the form's header or repeating in each record. That's key to the approach when one is dealing with a continuous form. And its not clear as to what you want locked - just the command button or other stuff too?

  3. #3
    mbake085 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    21
    I am very open to suggestions, however what I have works... almost. The problem is invoice data is recorded in advance of approvals and approvals could take a month. So I don't want the data being entered and effecting the budget query, unless the invoice is actually approved.

    I spent some time thinking about this and what I have is fairly complicated but here it goes...

    The button is repeating in each record, I like that. So as previously stated I took option two and have this button opening an unbound single form. Using default values this button opens the unbound for to the record you clicked prior, in the continuous. I added a INVOICE_ADDED Y/N field to the underlying table. I made this invisible in the continuous form and visible and locked in the single unbound form. The is a button in the footer of the unbound form asking "Do you really want to add this invoice?" If you click it runs two SQL queries, the first changes the INVOICE_ADDED value in the underlying table to Y and the second query inserts the data from the fields into the final invoice table. This is where I get stuck...

    I wanted to write a code that disables the button on the single unbound for in the INVOICE_ADDED field = Y.

    I tried this code:

    If INVOIVCE_ADDED.Value = 1 Then
    INVOICE_ENTRY.ADD_INVOICE.Enabled = False
    Else
    INVOICE_ENTRY.ADD_INVOICE.Enabled = True
    End If
    End Sub

    But it gets confused and so am I. Plus i'm not sure of the best area to place the code like On Update for the Y/N or On Current for the form.

    I'm stuck...

  4. #4
    mbake085 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    21
    Got it to work!

    Here is the code for the Add Invoice button in the unbound form:

    Private Sub ADD_INVOICE_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "INVOICE_ADDED"
    DoCmd.OpenQuery "INVOICE_ENTRY"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "INVOICE_APPROVER", acSaveYes
    DoCmd.Close acForm, "INVOICE_ENTRY", acSaveYes
    DoCmd.OpenForm "INVOICE_APPROVER"
    End Sub

    That is to ensure it updates... Probably did that wrong so any corrections are appreciated.

    This is in the unbound form's load procedure:

    Private Sub Form_Load()
    If DLookup("[INVOICE_ADDED]", "INVOICE_APPROVER", "[ID]=" & Forms![INVOICE_ENTRY]![ID]) = True Then
    Forms![INVOICE_ENTRY]![ADD_INVOICE].Enabled = False
    Else
    Forms![INVOICE_ENTRY]![ADD_INVOICE].Enabled = True
    End If
    End Sub

    Corrections/Tweaks are appreciated.

    Thanks,

    Matt

  5. #5
    mbake085 is offline Novice
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    21

    Final Solution

    Since people are always helping me I was hoping my final solution would help anyone else attempting something similar.

    Here are the steps:

    Choose the final table to send the data.
    Make a copy of that table.
    (If using a front end and back end db setup the final table is the linked table on the server and the copy of the table will only appear in the front ends)
    Add an auto number ID and Y/N titled INVOICE_ADDED field to the copied table.
    Create a continuous form from this table, make the ID and INVOICE_ADDED fields invisible.
    Create another single form based on the continuous, make the INVOICE_ADDED Y/N field visible, but locked.
    Add a button in the detail of the continuous form in the last column of the row.
    Add this code to the button:

    Private Sub OPEN__INVOICE_ENTRY_Click()
    Me.Refresh
    On Error GoTo OPEN_INVOICE_ENTRY_Click_Err
    Dim strWhere As String
    strWhere = "[ID]=" & Me.[ID]
    DoCmd.OpenForm "INVOICE_ENTRY", , , strWhere
    OPEN_INVOICE_ENTRY_Click_Err:
    Exit Sub
    End Sub

    Write the SQL queries:

    UPDATE INVOICE_APPROVER SET INVOICE_ADDED = 1
    WHERE [ID]=[Forms].[INVOICE_ENTRY].[ID];

    and

    INSERT INTO INVOICES ( DATE_ENTERED, PR, PO, IR, DESCRIPTION, ASSET_TAG, SERIAL, MAIN_COMPONENT, OTHER_BUDGET, LABOR_BUDGET, NOTES, ATTACHMENTS )
    VALUES ([Forms].[INVOICE_ENTRY].[DATE_ENTERED], [Forms].[INVOICE_ENTRY].[PR], [Forms].[INVOICE_ENTRY].[PO], [Forms].[INVOICE_ENTRY].[IR], [Forms].[INVOICE_ENTRY].[DESCRIPTION], [Forms].[INVOICE_ENTRY].[ASSET_TAG], [Forms].[INVOICE_ENTRY].[SERIAL], [Forms].[INVOICE_ENTRY].[MAIN_COMPONENT], [Forms].[INVOICE_ENTRY].[OTHER_BUDGET], [Forms].[INVOICE_ENTRY].[LABOR_BUDGET], [Forms].[INVOICE_ENTRY].[NOTES], [Forms].[INVOICE_ENTRY].[ATTACHMENTS]);

    Add a button in the footer of the single INVOICE_ENTRY form.
    Add this code:

    Private Sub ADD_INVOICE_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "INVOICE_ADDED"
    DoCmd.OpenQuery "INVOICE_ENTRY"
    DoCmd.SetWarnings True
    DoCmd.Close acForm, "INVOICE_APPROVER", acSaveYes
    DoCmd.Close acForm, "INVOICE_ENTRY", acSaveYes
    DoCmd.OpenForm "INVOICE_APPROVER"
    End Sub

    Then add this code to the On_Load of the single form:

    Private Sub Form_Load()
    On Error GoTo Form_Load_Err
    If DLookup("[INVOICE_ADDED]", "INVOICE_APPROVER", "[ID]=" & Forms![INVOICE_ENTRY]![ID]) = True Then
    Forms![INVOICE_ENTRY]![ADD_INVOICE].Enabled = False
    Else
    Forms![INVOICE_ENTRY]![ADD_INVOICE].Enabled = True
    End If
    Form_Load_Err:
    Exit Sub
    End Sub

    And it works.

    A lot of work for something that could be bounded to the final table, however I am stubborn .

    I am new to VBA and try to avoid it at all costs, so I'm sure there are some changes but for now it works well.

    Sincerely,

    Matt

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Disable master form close button
    By Carpy01 in forum Forms
    Replies: 3
    Last Post: 12-31-2010, 05:41 PM
  2. Use a command button to open a form
    By johnpaul in forum Forms
    Replies: 24
    Last Post: 09-23-2010, 12:29 PM
  3. Replies: 1
    Last Post: 07-27-2010, 02:27 PM
  4. how to disable command button
    By archie in forum Access
    Replies: 1
    Last Post: 08-27-2009, 11:11 PM
  5. Export Command Button in Form
    By jjmartinson in forum Forms
    Replies: 3
    Last Post: 08-25-2009, 01:28 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums