Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2015
    Posts
    15

    Answered: Go to old record, OR new...

    So, I am making a fairly simple outgoing mail registration system. Its based on one case, with many recievers. So I build it up as a main form and a subform. So far so good =) Now the tricky part. Every case have a uniqe case number, and I want to, if I write in a casenumber, to check if this already have been registered, and therefor I can add more recievers, or if not, it should make it a new record =)

    So, I've tried a few simple soulutions, but now need to think new. Thnx for the help ppl =)

  2. Best Answer
    Posted by Missinglinq

    "Replacing CaseNumber and YourTableName with your actual names:

    If CaseNumber is defined as Text

    Code:
    Private Sub CaseNumber_AfterUpdate()
    
     Dim TempCN As String
     
     If DCount("CaseNumber", "YourTableName", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then
      TempCN = Me.CaseNumber
      Me.Undo
      Me.Recordset.FindFirst "[CaseNumber]= '" & TempCN & "'"
     Else
      TempCN = Me.CaseNumber
      Me.Undo
      DoCmd.GoToRecord , , acNewRec
      Me.CaseNumber = TempCN
     End If
    
    End Sub

    If CaseNumber is defined as a Number

    Code:
    Private Sub CaseNumber_AfterUpdate()
    
    Dim TempCN As Integer
    
     If DCount("CaseNumber", "YourTableName", "[CaseNumber]=" & Me.CaseNumber) > 0 Then
      TempCN = Me.CaseNumber
      Me.Undo
      Me.Recordset.FindFirst "[CaseNumber]= " & TempCN
     Else
      TempCN = Me.CaseNumber
      Me.Undo
      DoCmd.GoToRecord , , acNewRec
      Me.CaseNumber = TempCN
     End If
    
    End Sub


    As you move off of the CaseNumber Textbox, Access will either go to the existing Record or go to a New Record and fill in the entered CaseNumber.

    Linq ;0)>"


  3. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    in a continuous form of all the cases, enter # into text box, txtFind
    then filter for that rec. if not there, start typing in new rec.

    Code:
    sub txtFind_afterupdate()
      if Isnull(txtFind) then 
          me.filterOn = false  
      else
         me.filter ="[CaseNum]='" & txtFind & "'"
          me.filterOn = true
      endif  
    
    end sub

  4. #3
    Join Date
    Mar 2015
    Posts
    15
    Not quite what I was looking for.

    I want to type in a number in txt_case

    And if this casenumber already exist, a pop up msg box wich says: this casenumber already exists, pls add the new recievers.

    It then looks up this case and i will be able to add a new reciever in the subform, and also too see the old recievers

  5. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    FoundNumber = Dcount("*","TheTableName","theFeildName = " & me.thistextbox )

    if it a text value

    FoundNumber = Dcount("*","TheTableName","theFeildName = '" & me.thistextbox & "'")


    if FoundNumber = 0 then

    Else
    msgbox("this casenumber already exists, pls add the new recievers.")
    End if
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #5
    Join Date
    Mar 2015
    Posts
    15
    Getting close. This found back to the old record, but did not pick up the already registered recievers in the sub form.

    So far I got this...:

    On Error GoTo myErr
    Dim strsql As String
    Dim rs As dao.Recordset
    Dim lngsvar As Long


    strsql = "SELECT forsendelse.casenumber, case.rom, case.date, case.reciever, case.district, case.sender, case.intern, case.hand, case.recommanded, case.recnr " & _
    "WHERE (((Forsendelse.casenumbder)=" & Me.txt_casenr& ")) ;"


    Set rs = CurrentDb.OpenRecordset(strsql)


    If rs.RecordCount >= 0 Then
    lngsvar = MsgBox("this casenumber is used before, Do you want to get the old information?", vbQuestion + vbOKCancel, "Auto: Case")
    If lngsvar = vbOK Then
    Me.txt_casenr.Value = rs.Fields("casenumber")
    Me!fSak.Form.txt_dato.Value = rs.Fields("date")
    Me!fSak.Form.comb_mottager.Value = rs.Fields("reciever")
    Me!fSak.Form.comb_pd.Value = rs.Fields("district")
    Me!fSak.Form.comb_sendt.Value = rs.Fields("sender")
    Me!fSak.Form.bol_int.Value = rs.Fields("intern")
    Me!fSak.Form.bol_hand.Value = rs.Fields("hand")
    Me!fSak.Form.bol_rek.Value = rs.Fields("recommanded")
    Me!fSak.Form.txt_reknr.Value = rs.Fields("recnr")
    Me!fSak.Form.cmb_rom.Value = rs.Fields("rom")

    End If
    End If

    myExit:
    Exit Sub
    myErr:
    MsgBox Err.Description
    GoTo myExit

  7. #6
    Join Date
    Mar 2015
    Posts
    15
    So, actually, after trying to solve this (still unsolved) I think I found a better way to explain what I actually want =)

    When I type in "casenumber" and the casenumber already exists, I want to go to the POST so I can fill in new information.

    If it does not exist, then go to new record... =)

  8. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Replacing CaseNumber and YourTableName with your actual names:

    If CaseNumber is defined as Text

    Code:
    Private Sub CaseNumber_AfterUpdate()
    
     Dim TempCN As String
     
     If DCount("CaseNumber", "YourTableName", "[CaseNumber]='" & Me.CaseNumber & "'") > 0 Then
      TempCN = Me.CaseNumber
      Me.Undo
      Me.Recordset.FindFirst "[CaseNumber]= '" & TempCN & "'"
     Else
      TempCN = Me.CaseNumber
      Me.Undo
      DoCmd.GoToRecord , , acNewRec
      Me.CaseNumber = TempCN
     End If
    
    End Sub

    If CaseNumber is defined as a Number

    Code:
    Private Sub CaseNumber_AfterUpdate()
    
    Dim TempCN As Integer
    
     If DCount("CaseNumber", "YourTableName", "[CaseNumber]=" & Me.CaseNumber) > 0 Then
      TempCN = Me.CaseNumber
      Me.Undo
      Me.Recordset.FindFirst "[CaseNumber]= " & TempCN
     Else
      TempCN = Me.CaseNumber
      Me.Undo
      DoCmd.GoToRecord , , acNewRec
      Me.CaseNumber = TempCN
     End If
    
    End Sub


    As you move off of the CaseNumber Textbox, Access will either go to the existing Record or go to a New Record and fill in the entered CaseNumber.

    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

  9. #8
    Join Date
    Mar 2015
    Posts
    15
    YES! Tis did the trick =) Thnx =)

  10. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    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
  •