New Record

[quietstormtw]

Hi all, I have finally reached my wits end...I need some major assistance.The code below is what I have been able to come up with so far, however, it is not taking me to my ultimate goal.

What I would like to do is:

1) Have the users enter the case number the they would like to add or modify
2) Use the case # entered to interogate the query to find is CaseNBR is present
3) If the CaseNBR is not present, return an error message
4) If the Case NBR is present, have the code also look at the CaseNo field, which should be equal to CaseNBR
5) If the Case # entered matches both, CaseNBR & CaseNo, the open the testCAR form in Edit mode
6) If the Case # entered matches the CaseNBR, but not the CaseNo, have the form open in ???? mode...and populate the CaseNo field with the number from CaseNBR


Private Sub Preview_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "Select qryMain.*" & _
"From qryMain"

strWhere = "Where"

strOrder = "Order BY qryMain.CaseNBR"

If Not IsNull(Me.txtCase) Then
strWhere = strWhere & " (qryMain.CaseNBR) = " & Me.txtCase & " AND "
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Set qryDef = dbNm.QueryDefs("qryDateRange")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder

Dim rsCnt As Variant
rsCnt = DCount("[CaseNBR]", "qryDateRange")

If rsCnt = 1 Then
DoCmd.OpenForm "testCar", acNormal
MsgBox "Form Add", vbOKOnly
If IsNull([Forms]![testCar]![CaseNo]) Then
Forms!testCar!CaseNo = Me.txtCase
End If
ElseIf rsCnt = 0 Then
MsgBox "There is no Case # for this request. Please ensure that an address is entered in Paragon", vbOKOnly, "No Data"
Exit Sub
ElseIf IsNull(Me.txtCase) Then
MsgBox "Please enter a Case # for this request", vbOKOnly, "No Case #"
End If
DoCmd.Close acForm, "frmtestCAR", acSaveYes

End Sub

Any advise is welcomed...and appreciated!!

 

[quietstormtw]

Thanks to anyone who attempted to resolve my issue...I've figured it out.

Thanks anyway

 

[simon551]

hi quiet,
I'm curious how you worked that. I'm pretty new but I was trying to figure out how to have a form open in a new record mode but not in dataentry. so the user can enter data by default and review also.

later,
simon

 

[ProgramError]

I was trying to figure out how to have a form open in a new record mode but not in dataentry

This is what you need....

DoCmd.OpenForm "FormName", acNormal
DoCmd.GoToRecord , , acNewRec


Program Error
Programmers do it one finger at a time!

 

[TheAceMan1]

How are ya quietstormtw . . . . .

Forgive me, but your logic was just fine, code was . . . ?

Just one question: When you open testCar and [purple]txtCase = CaseNBR and CaseNo[/purple],is it your intent to just open the form or do you want to go a record matching [blue]CaseNBR or CaseNo?[/blue]

In any case here's what I came up with:

[blue]Private Sub Preview_Click()
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   Dim Msg As String, Style As Integer, Title As String, DL As String

   Dim db As Database
   DL = vbNewLine & vbNewLine
   
   If IsNull([purple][b]Me.txtCase[/b][/purple]) Then [green]'If true no sense going any farther.[/green]
      Msg = "No Case# entry for this request!" & DL & _
            "Enter a Case# and try again . . ."
      Style = vbCritical + vbOKOnly
      Title = "No Case# Error!"
      MsgBox Msg, Style, itle
   Else [green]'Continue & Setup SQL.[/green]
      SQL = "Select qryMain.* " & _
            "From qryMain " & _
            "Where [[purple][b]CaseNBR[/b][/purple]] = " & [purple][b]Me.txtCase[/b][/purple] & ";"
      Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
      
      If rst.EOF Then [green]'If true [CaseNBR] = Me.txtCase not in recordset.[/green]
         Msg = "There is no Case# for this request." & DL & _
               "Please ensure that an address is entered in Paragon"
         Style = vbCritical + vbOKOnly
         Title = "Data Not Found Error! . . ."
         MsgBox Msg, Style, Title
      Else [green]'[CaseNBR] = Me.txtCase OK.[/green]
         DoCmd.OpenForm "testCar"
         If Me![purple][b]txtCase[/b][/purple] = rst![purple][b]CaseNo[/b][/purple] Then [green]'txtCase = CaseNBR and CaseNo[/green]
            [green]'I think you want to goto CaseNBR or CaseNo in testCar.
            'Not sure . . .[/green]
         Else
            Forms!TestCar.SetFocus [green]'txtCase = CaseNBR and Not CaseNo[/green]
            DoCmd.RunCommand acCmdRecordsGoToNew
            Forms!TestCar![purple][b]CaseNo[/b][/purple] = rst![purple][b]CaseNBR[/b][/purple]
         End If
      End If
      
      Set rst = Nothing
   End If

   set db=nothing
            
End Sub[/blue]

See Ya! . . . . . .