Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2006
    Posts
    11

    Unanswered: Filtering Within A Form

    I am trying to filter to a specific record within a form. The user enters a barcode into the form and I want to move to that record. However access tries to update the current record and finds a duplicate rather than goto the record ??

    I have created a query which works correctly I just want to implement this in a form.


  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    how are you capturing the barcode value?
    what is your design process to retrieve the barcode value?

    it sounds to me like you are transferring the barcode value to a bound control and then trying to update the data.

    I would have expected the scanner to trigger an event which runs a query.. to see if the record already exists, if so retrieve that record, else create a new record.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2006
    Posts
    11
    I have tried to run the query using the onchange for the barcode textbox but this again tries to process the currently in view record instead of moving to the required record.

    This is the query
    SELECT Product.Barcode, Product.Supplier, Product.Type, Product.Size, Product.Width, Product.Sex, Product.Cost, Product.[Sale Price], Product.Picture, Product.Qty, Product.[Date Stocked]
    FROM Product
    WHERE (((Product.Barcode)=([me]![tbxControl].[Text])));

    I read on another site the last bit of code which reads the uncommitted value from the current text box in this case the barcode.

  4. #4
    Join Date
    Dec 2006
    Posts
    53
    Isn't there a wizard that does this? Insert a pull-down list, and a wizard comes up walking you through setting it up.

  5. #5
    Join Date
    Nov 2006
    Posts
    11
    Insert a pull-down list ?? in the form ?? but i want to enter a barcode not select it from a list.

  6. #6
    Join Date
    Dec 2006
    Posts
    53
    Why not a pull-down list? Populate it with the contents of the field you will be searching, might even get autocomplete to save you some work.

  7. #7
    Join Date
    Nov 2006
    Posts
    11
    because the user will be using a barcode wand to enter the barcode this is for use in a shop at the checkout to add/remove the item from stock as it is sold or put into stock.

  8. #8
    Join Date
    Dec 2006
    Posts
    53
    Ahhh. First time I've worked with that technology.

    When I try this with a combo box lookup, it's the AfterUpdate event that has an Event Procedure. I can't imagine how it would be any different. That procedure looks like this:

    Code:
    Private Sub Combo21_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
    
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[SurveyID] = '" & Me![Combo21] & "'"
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    I think it might be that Clone step that is missing from your code. Actually, I don't think you need that elaborate query anyhow.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Do us a favour go into the form's design, right click the box you want the bar-code in then choose properties, then click the data tab and tell us what it says.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Nov 2006
    Posts
    11
    control source = barcode
    enabled = yes
    locked = no
    filter lookup = database default

    all other fields are empty.

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    That's probably the source of your problem by having the control source as the barcode field means that when you enter a value in this textbox it tries to write the data to that field in the table (this is called binding) which is great if you want to edit data but usless for a serch critera.

    this is what healdem was trying to say but you missunderstood him.

    you need to clear the control source field or add a new textbox that isn't bound to the field.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Nov 2006
    Posts
    11
    Ah thankyou so much I have spent a long time trying different approaches to do this. That appears to work as I want.

    Thankyou

  13. #13
    Join Date
    Dec 2006
    Posts
    53
    Does the OnChange event require you to leave the field? Is that the purpose of the AfterUpdate event?

  14. #14
    Join Date
    Dec 2006
    Posts
    53
    IC. The AfterUpdate event takes place when the data in the form field actually updates the table data, which does not take place until the field loses focus.

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id suggest a different methodology
    dont allow the scanner to update a bound control

    use a search box in say the form footer or header that gets a value for the scanner. usually that sort of code is specific to the device.

    when that value chanegs trigger a bit of SQL or a function that does a search
    set the recordsource of the form equal to that search.

    if the record is not found it should change the form to add mode, if the record is found then display that recod.

    IE break the link between the bound control / and the record associated with that control and the scanner
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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