Want to prompt user to enter date with VB 3

[dbar10]

I have the following code, but being new to VB, I need help so I don't mess it up. Now the code uses the system date to Insert into all records. But I need to have the code prompt the user for the FromDate instead or I could put an unbound text box called FromDate on the [Authorization Client Form] properly formatted for Short Date. But I would need the code to warn if the textbox is Null and stop until the box is filled. Can someone please help me with this? I would sure appreciate it.

Private Sub Command74_Click()
Dim strAuthID As String
Dim strClientID As String
Dim strSQL As String

DoCmd.Echo False

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO HCWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO PCWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO APCWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO RespWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO LPNWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO RNWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO PTWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO OTWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO SLTWeekHours (payAuthID,ClientID,FromDate) " & _
"VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

DoCmd.Echo True

End Sub

 

[PWise]

Private Sub Command74_Click()
Dim strAuthID As String
Dim strClientID As String
Dim strSQL As String

DoCmd.Echo False
[COLOR=red][b]if not isdate(me.fromdate) then
     msgbox "Enter valid Date!"
     me.fromdate.setfoucs
     exit sub
end if[/b][/color]
strAuthID = [Forms]![Authorization Client Form]![PayAuthID]
strClientID = [Forms]![Authorization Client Form]![ClientID]
strSQL = "INSERT INTO HCWeekHours (payAuthID,ClientID,FromDate) " & _
  "VALUES ('" & strAuthID & "','" & strClientID & "', #" & Date & "#); "
CurrentDb.Execute strSQL, dbFailOnError

Me.Requery

 

[dbar10]

Thanks pwise. I put in your code and built the FromDate text box. I am now getting an error:

Run-Time error '94':
Invalid use of Null

Debugger is highlighting this line:
strAuthID = [Forms]![Authorization Client Form]![PayAuthID]

The PayAuth ID is showing on the main form, so I don't know what this means. Can you help?

 

[PWise]

Well!

strAuthID is dimmed as a string

so it can not be null

try

strAuthID = nz([Forms]![Authorization Client Form]![PayAuthID],"")

 

[kjv1611]

And what if you change

strAuthID = [Forms]![Authorization Client Form]![PayAuthID]

to

strAuthID = [Forms]![Authorization Client Form]![PayAuthID][highlight white].Value[/highlight]

?

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Actually scratch that. I bet you need to change it to:
strAuthID = [Forms]![Authorization Client Form]![PayAuthID][highlight white].Text[/highlight]

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

Thank you but the PayAuthID is never Null and it is not null now even though I'm getting this error. Curious. I cna't ever have that PayAuthID as null. This worked just fine before I put in the new code for the FromDate. Any other ideas?

 

[kjv1611]

Did you try my suggestion?

The reason for the difference would be that the recordset value could be null while the text actually has a value. The text is what you see.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

YesI tried it. The .text brought the error message:

Run-time error '2185':
you can't reference a property or method for a control unless the control has the focus.

Debug highlights:

strAuthID = [Forms]![Authorization Client Form]![PayAuthID].Text

Then I tried with .Value and it runs but it still places the system date instead of the [FromDate] text box on main form.

Any ideas?

 

[kjv1611]

What you need to do to correct that one is this:

[Forms]![Authorization Client Form]![PayAuthID].SetFocus
strAuthID = [Forms]![Authorization Client Form]![PayAuthID].Text
[green]'Then reset focus to whatever control you'd prefer to have focus.[/green]
[Forms]![Authorization Client Form]![[i]SomeOtherControl[/i]].SetFocus

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

it still places the system date instead of the [FromDate]
Replace this:
, #" & Date & "#)
with this:
, #" & Me!FromDate & "#)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886