1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Search form not opening records properly...

Discussion in 'Business Applications' started by ukmonkeyman, Nov 19, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    Basicly I have been given the task of building a system to manage accounts and log calls and I have most of what to do already mapped out and having done similar things in parts with Access before I thought it would be a doddle but a few steps in and I have already come up against a brick wall. I have a form with several search fields and that works fine, no hitches at all and when you click on a button next to the top record it opens the customers details in a new form for editing, that works fine also. The problem I am having occurs when I get more than one result from the search and I try to click the button next to any record except the first and the problem is that the form I want to load is not loading anything, its just a blank form. No text boxes, no labels, nothing but the colours and the picture in the corner. I have put the code on the form to show me what data is going through and when following through with the first record it is showing me the correct data was transfered but when clicking any other record I am getting nothing. I have tried hunting for a solution for this but find it hard to know what to type to get the right information for my problem so I am sorry if this has been resolved before. Also forgive me for my patchy knowledge but I have taught myself everything I have done so far so it is probably just a step I have missed somewhere along the line.

    Any Ideas?

    Code:
    'In Search Form on button click
        Dim strWhere As String
        If IsNull(Me.OrderRef) Then
        Else
            strWhere = "[OrderRef] = " & Me.OrderRef
            DoCmd.OpenForm "fsOpenOrder", WhereCondition:=strWhere
        End If
    
    'In Order Form on load
        If Me.Status = "Closed" Then
            MsgBox "This order has been closed and you are no longer able to  edit any information. If you need to re-open this account then please  contact the database admin.", vbInformation, "Closed Order."
            Me.Price.Locked = True
            Me.Items.Locked = True
            Me.Notes.Locked = True
            Me.Dispatched.Locked = True
            Me.DispatchDate.Locked = True
            Me.Status.Enabled = False
            Me.Payment.Locked = True
            Me.PaymentCleared.Locked = True
            Me.ID.Locked = True
        Else
            Me.Price.Locked = False
            Me.Items.Locked = False
            Me.Notes.Locked = False
            Me.Dispatched.Locked = False
            Me.DispatchDate.Locked = False
            Me.Status.Enabled = True
            Me.Payment.Locked = False
            Me.PaymentCleared.Locked = False
            Me.ID.Locked = False
        End If
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    2 questions, what type of form is the search form, is it in "Continuous Forms mode" or Single form mode?
    Where is the Button on the Form?
     
  3. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    Continuous forms mode and the button is on the details section in line with the data, i also tried putting the code on the double click function of the details section and still no luck.
     
  4. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    Something new I found. It is showing only the first record in my table, if my search omits my tables first record it is not opening the first search result in the new form as with my original post. So this is something to do with it pulling data from the table right?
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    OK, it is a pity we are so far apart timewise as this is taking longer than it should.
    I would suggest that you add this line to your code after
    strWhere = "[OrderRef] = " & Me.OrderRef
    msgbox "strwhere = " & strWhere " OrderRef = " & Me.OrderRef

    This will check that you are getting data and what the data actually is. My concern is that the button is not interacting with the Record in the Continuous form.

    Can you post this database with some dummy data in Access 2000-2003 format?
    I have used this form opening technique quite a bit and the Syntax is also quite important, so did you get the Command Button Wizard to create it?
    You could try strWhere = "[OrderRef] = '" & Me.OrderRef & "' "
     
  6. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    yes, give me 30 mins, just finishing up something else then will put some dummy data in. thank you so much for your help so far. it is greatly appreciated.
     
  7. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    ok, attached is what I have got so far. I am sure you will see the problem straight away but I am coming to the conclusion that it is an issue with the relationship with the table. I am going to keep trying things my end until I hear back from you and thanks again.
     

    Attached Files:

  8. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    OK, I think I have found the mistake myself and I think my solution to the task originally was the wrong way of going about it. Maybe I should be passing the info across via openargs and filtering rather than opening the form with a where condition?
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I don't think so, your main problem is the Orders form does not show any data at all when you open it.
    I have disbaled the VBA and it still doesn't show any records
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry I was looking at the wrong form.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have it working now.
     
  12. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Take a look at this version.
    I have changed the Form's record source to a Query, it was using an SQL with a where clause.
     

    Attached Files:

  13. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    ahhh, ok so its best to query first and use the query to do searches then?
     
  14. ukmonkeyman

    ukmonkeyman Thread Starter

    Joined:
    Nov 19, 2011
    Messages:
    17
    and thank you very much for your help, makes sense now im looking at it done properly. I owe you a beer. :)
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I always use queries for forms and reports, you can check the records, order them filter them if necessary and then create the form or report and recheck the records.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1027596

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice