Results 1 to 14 of 14
  1. #1
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151

    Question Changing a forms's behaviour

    Hi all



    I've been asked to change the way an existing form works. Originally the form was a stand alone form that was designed for bulk entry for multiple customers.

    I have changed it in to a pop up sub form Training & Services that, when opened from the Customer form, displays records for a customer based on the main form. They are linked on CustomerID. I can use the record navigation buttons at the bottom of the form to look through all the records for that customer.

    Code:
    Private Sub Training_Serv_Click()
    On Error GoTo Err_Training_Serv_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
    
        stDocName = "Training & Services"
        stLinkCriteria = "[CustomerID]=" & "'" & Me![CustomerID] & "'"
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_Training_Serv_Click:
        Exit Sub
    
    Err_Training_Serv_Click:
        MsgBox Err.Description
        Resume Exit_Training_Serv_Click
        
    End Sub
    This works fine, however when I go to enter a new record, the CustomerID field is blank and you need to select the customer before entering the new service record. This is currently a combo box.

    What I would like to have the form do is only allow me enter new records for just that customer. ie. The combo box basically becomes redundant and the form wll write new records just for the customer on the main form.

    What is the best way of achieving this?

    BTW, I am aware of the problems with using spaces & characters in field and form names and am currently fixing this in the DB, but it's a big job.

    Thanking you for your input.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can have code set DefaultValue of combobox with customer ID and lock combobox so users can't edit. Advantage of combobox over textbox is even though combobox saves CustomerID, combobox can display customer name.

    A popup form is not a subform, it is still a stand alone form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Hello again June7

    Thank you. I didn't even think of setting the default value. That works exactly as I wanted. Thanks also for clarifying about the pop-up. I thought that since the training record was called from the customer form, it was a sub form of it because it's sort of bound by CustomerID.

    Now, I have a combo box on that form form from which the operator can choose which machine to apply the new service record to. (many customers have multiple machines)

    So basically I need the combo box display records just for the customer ID currently displayed on the service form.

    At the moment the combo box shows every Machine No for all customers and is not filtering for just the selected customer. The query builder for the combo box created this;

    Code:
    SELECT [Customer Machine Components].MachineNo FROM Customers INNER JOIN [Customer Machine Components] ON Customers.CustomerID = [Customer Machine Components].CustomerID;
    and I have to control source set to Machine No.

    I've seen example of filtering combo boxes based on another combo box, but not on a form.

    Been playing around with this approach but it isn't working.

    Code:
    SELECT [Customer Machine Components].[MachineNo] FROM Customers WHERE (([Customer Machine Components].[CustomerID])=[Forms]![Training & Services]![CustomerID]);

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A combobox is never filtered 'based on a form', have to reference a field or control bound to field.

    Combobox is on [Training & Services] (ugh, there's those spaces and characters) form?

    Name textbox bound to CustomerID different from the field, such as tbxCustomer, then try Combobox RowSource:
    SELECT [MachineNo] FROM Customers WHERE [CustomerID]=[tbxCustomer];

    Will need code in event(s) that requery the combobox. Combobox GotFocus event is one

    "isn't working" means what - error message, wrong result, nothing happens?
    Last edited by June7; 06-20-2019 at 01:03 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Hi June7

    Quote Originally Posted by June7 View Post
    Combobox is on [Training & Services] form?
    Yes, that is correct

    Name textbox bound to CustomerID different from the field, such as tbxCustomer, then try Combobox RowSource:
    SELECT [MachineNo] FROM Customers WHERE [CustomerID]=[tbxCustomerID];
    OK. MachineNo is actually in the Customer Machine Components table (I know, spaces..), so I changed the code to

    Code:
    SELECT [MachineNo] FROM [Customer Machine Components] WHERE [CustomerID]=[tbxCustomerID];
    And that seems to work.

    Will need code in event(s) that requery the combobox. Combobox GotFocus event is one
    What is the purpose of this? I don't mind the combo box being blank when the new record opens, because it makes the operator stop and think.

    "isn't working" means what - error message, wrong result, nothing happens?

    Yes sorry, not very helpful. I meant that the combo box was blank.


    Anyway, I'll do some more fine tuning and testing, but I think that you have helped me again.

    Thank you very much.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Combobox should not be blank when form first opens to existing record because combobox list should show machines associated with that customer.

    Maybe you will not need Requery for your situation. If user can navigate to a different customer, combobox list will not be appropriate and Requery will refresh the list to show that customer's machines.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by June7 View Post
    Combobox should not be blank when form first opens to existing record because combobox list should show machines associated with that customer.

    Maybe you will not need Requery for your situation. If user can navigate to a different customer, combobox list will not be appropriate and Requery will refresh the list to show that customer's machines.
    Hi June7

    Thanks for feedback. I'll have a think about your suggestion and maybe change my approach a little.

  8. #8
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Hi June7

    Quote Originally Posted by June7 View Post
    Combobox should not be blank when form first opens to existing record because combobox list should show machines associated with that customer..
    I'm curious to know why my combo box is blank when I open an existing record, where it should have the value for that record.

    On the same form I have a text box that correctly shows the MachineNo for the current record, so why doesn't the combo box? You mentioned having to requery the combo box in post 4 OnGotFocus event but that doesn't seem to resolve the issue.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Hi June7

    Please see attached.NEW WIP Multicam Newcastle 2010 -v11.zip

    There is a text document in the zip file. It's the same Db I attached previously.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The combobox is not bound to [Machine No] field. Can only show value when opening form if bound to field. Don't need code to save selected item to field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by June7 View Post
    The combobox is not bound to [Machine No] field. Can only show value when opening form if bound to field. Don't need code to save selected item to field.
    Hi June7

    Of course it can only show value when opening form if bound to field. I can't believe I left that out, especially since the text box under it is correct

    Very grateful for your help. Sorry to have wasted your time.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, if you want combobox used to enter search criteria, then it should NOT be bound because that would change value in table.

    Not seeing any search code associated with that combobox so guess it should be for data entry.

    There's another textbox for [Machine No] at upper right set not visible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    sheusz is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2015
    Posts
    151
    Quote Originally Posted by June7 View Post
    Well, if you want combobox used to enter search criteria, then it should NOT be bound because that would change value in table.

    Not seeing any search code associated with that combobox so guess it should be for data entry.

    There's another textbox for [Machine No] at upper right set not visible.
    Hi June7

    The combo box is not used for searching, it will be used for adding new records and will be locked for editing (based on user access levels). The other hidden text box was a remnant of something else I was trying and is now deleted.

    Thanks again for your help and input.

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

Similar Threads

  1. Changing Fonts on the fly in Forms
    By pharrison74 in forum Forms
    Replies: 2
    Last Post: 03-23-2016, 03:04 PM
  2. Replies: 4
    Last Post: 02-13-2016, 06:00 PM
  3. Problem with forms after changing table
    By winterh in forum Forms
    Replies: 5
    Last Post: 04-20-2012, 08:24 PM
  4. Changing Forms with Search
    By Zerdan in forum Forms
    Replies: 1
    Last Post: 05-31-2011, 10:03 AM
  5. Globally changing colors on forms
    By pwdpwd in forum Programming
    Replies: 2
    Last Post: 11-23-2010, 09:06 PM

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