Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2014
    Posts
    1

    Unanswered: Form help - search for existing record, if new, add new row

    Greetings all,

    I'm relatively new to Access, but have other db experience.

    I'm trying to create a very simple db/form that contains a product PartNum, SerialNum, date, and a few Yes/No fields based on needed repairs. If the SerialNum is not already in the db, it will create a new record. If the SerialNum is already in the db, it will simply open that record for editing.

    Basically, each board will be scanned in at receipt (new record created) and needed repairs will be noted. The boards will then head to the next station where the repair tech will scan the board again (populating the form with the existing record) so that repair and testing data can be entered.

    The only manual I have is The Missing Manual, and I haven't found anything like this mentioned, unless I missed something.

    I'm assuming this is a relatively simple task, but I have no idea to go about making this happen.

    Thank you for any and all help!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Question: Is it possible for two or more different parts to have identical Serial Numbers, or will any given Serial Number always be unique?

    Welcome to dBforums!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also note that this is double-posted here

    http://www.accessforums.net/forms/se...tml#post257733
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Missinglinq View Post

    ...will any given Serial Number always be unique...
    The double-post of this question appears to confirm that this Field will, in fact, be unique, so here's a hack (also posted on the double-post) for accomplishing this.

    Create an Unbound Textbox in your Form Header and name it txtSerialSearch. Then use this code:
    Code:
    Private Sub txtSerialSearch_AfterUpdate()
    
     Dim rst As Recordset
    
     Set rst = Me.RecordsetClone
    
     rst.FindFirst "[SerialNum] = '" & Me.txtSerialSearch & "'"
     
      If Not rst.NoMatch Then
          Me.Bookmark = rst.Bookmark
       Else
        DoCmd.GoToRecord , , acNewRec
        Me.SerialNum = Me.txtSerialSearch
       End If
    
    rst.Close
    
    Set rst = Nothing
    
    End Sub

    This assumes that SerialNum is defined as Text. I and others generally define a Field like this as Text, even if it only contains digits, unless it is going to be used for math operations. But if you want it to be defined as a Number, replace

    rst.FindFirst "[SerialNum] = '" & Me.txtSerialSearch & "'"

    with

    rst.FindFirst "[SerialNum] = " & Me.txtSerialSearch

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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