Page 2 of 2 FirstFirst 12
Results 16 to 29 of 29
  1. #16
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Hey, I managed to implement your solution. It does the trick, but there is a slight problem as I will try my best to detail below.

    Main Form: Estimate Details
    Tab: tabEstimateData
    Page: Products Page
    Subform: Estimate Products Subform

    As you can see, the subform is in the tabbed page.

    The subform is a datasheet of all the products for that estimate. Each product has its own row and the way I have designed the form, the search text box is a textbox for each row.

    Problem is, that anything typed into the search box for any row is replicated in all rows including the "new record row" (although it doesn't commit anything to the table) and the combo box of each row is filtered accordingly.



    So if the selected product in the combo box of the first row doesnt contain part of the string from the text box, then the combo box is just empty for the first row, although it doesnt affect the chosen product for that row.

    How can I fix this? Should I have the subform work as a continuous form and configure it so that it looks and behaves like a datasheet but each row is seperate?

    Thanks

    Code:
    Option Compare Database
    
    Private Sub Form_Load()
        Me.Product_Selector.ControlTipText = "Click on the down arrow" & vbNewLine & "to view products"
        Me.Product_Search_Change
    End Sub
    
    Sub Product_Search_Change()
        On Error Resume Next
        Dim StrSql              As String 'SQL statement for the record source
        Dim sText               As String 'Contents of the criteria control
        sText = Trim(Me.Product_Search.Text)
        
        If (sText = "") Then
            sText = "*"
        End If
        
        StrSql = "SELECT [Products Extended].[Product ID], [Products Extended].Product FROM [Products Extended]"
       
        'Is there any text to test?
            If Not sText = "" And IsDelOrBack = False Then
                    
                    StrSql = StrSql & "WHERE [Product] Like '*" & sText & "*';"
                                    
                    'Refresh the rowsource with the new SQL
                    Me.Product_Selector.RowSource = StrSql
                        
            End If
            'Requery the list box to show results
            Me.Product_Selector.Requery
    End Sub
    
    Private Sub Product_Selector_AfterUpdate()
        'Take current sale price and stick it into the estimate items table
    End Sub

  2. #17
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Can you post a smaple of your db (pre 2007)

    David

  3. #18
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    I'm trying to save it in 2003 format, but it's saying I cannot because the system uses features that are only available in 2007 format.

    I'm uploading the 2007 version with data altered for obvious reasons, let me know if that will be okay or not. (had to delete lots of forms and tables to make it small enough after compact, repair and zipping!!)

    Thanks

  4. #19
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Sorry do not have 2007 on this machine, hopefully some who has can look at it for you.

    David

  5. #20
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    I understand. I'll make a screen capture video if you like of the system while I use it to show you how it works

    Thanks for helping so far mate, much appreciated while i'm totally lost here

  6. #21
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Good News!! Got the database to save in 2003 format.

    Going to just go through the data and upload it. I have another problem, but I shall explain that when I upload.

    Regards

    Asif

  7. #22
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Okay I'm back.

    So the first problem is the combo box filtering. If you go to any estimate or job, you will be able to see what I mean on the subform. You can add as many products to an estimate or job, but if you look, when data is typed into any of the text boxes, if is replicated into all the textboxes in that subform and it filters all the combo boxes in that subform as well. Im thinking either have one single text box and product selector combo box on the parent form that adds to the subform or something better where each roq is independent (continuous form to look like datasheet?)

    Also, when I make a change on the parent form, I want the change to be reflected in the subform. For example, when I change the area size, it should change the area size of the estimate_labour_subform but it only does that if i click into the subform.

    Finally, when I make a new job, the total and payment information subform (and the total subform on the estimate) are not available till i close the form and reopen it.

    Any clues?

    Thanks for offering to help David, this is really a thorn in my side at the moment.



    PS: File compressed to RAR archive and then the RAR was put into a ZIP archive so that I can upload it here (file type and size limits)

  8. #23
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Not all people have WinRar and again not all users have A2007 if you can upload a cut down version in pre 2007 I or others can look at it.

    David

  9. #24
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Sorry mate, my mistake. I'll upload to Rapidshare:

    http://rapidshare.com/files/29636352...Sample.mdb.htm

    My apologies again.

  10. #25
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Hi David

    Did you get a chance to download the DB? I managed to sort out the "Change on parent form to be reflected on subform" issue.

    I am just baffled to how I can get each row in a datasheet to behave independently when it comes to additional items that I add in such as text boxes and check boxes.

    Thanks Mate

  11. #26
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    I managed to do this in the end. I put an actual field in the table which was a checkbox and I was able to work with that.

  12. #27
    cathyntlai is offline Novice
    Windows Vista Access 2003
    Join Date
    Jul 2010
    Posts
    1
    Could you please post the mdb file again? Thanks in advance.

  13. #28
    Pimped is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2009
    Posts
    25
    Quote Originally Posted by cathyntlai View Post
    Could you please post the mdb file again? Thanks in advance.
    Don't know if i still have it. What do you need to know?

  14. #29
    intefix is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2015
    Posts
    1
    Hi guys... i'm interesting about that...2015 hehehe...
    If is it possible send me mdb file or VB code in my e m a i l.
    h_kontos[at]yahoo[dot]com

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. filter form based on combo box
    By lloyddobler in forum Forms
    Replies: 8
    Last Post: 09-10-2009, 07:33 AM
  2. Combo Box Filter
    By jgelpi in forum Programming
    Replies: 3
    Last Post: 07-27-2009, 12:54 PM
  3. Filter Form records with Combo Box????
    By jgelpi in forum Forms
    Replies: 0
    Last Post: 05-19-2009, 07:05 AM
  4. Replies: 0
    Last Post: 02-27-2009, 01:27 PM
  5. Applying a filter to a combo box
    By bugchaser in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 02:37 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