Look up help

  • Thread starter Thread starter BLW
  • Start date Start date
B

BLW

I have a form where I enter in accident information. When I save this form,
I want it to look into a another table (Accident Investigation) to see if an
investigation has been completed that matches the Case #. If no
investigation is found, I want it to add a task to the task list to remind
the user to complete the investigation.

Here is my code. What am I doing wrong? I keep getting "You canceled
Previous Operation"


Dim stDocName As String

If DCount("[Case]", "[Accident Investigation]", "[Case]=Me.[Case]") = 0
Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms(stDocName)![Task] = "Accident Investigation on " & Me.Employee
Forms(stDocName)![Due Date] = Me.Accident_Date + 3
DoCmd.Close
End If
DoCmd.Close
 
Well, for starters, I don't see that you have populated either of the
varialbes for opening the form. stDocName is Dimmed but not populated.
stLinkCriteria is not even Dimmed which means you are not using Option
Explicit, which you really should be. But, you don't really even need to
open a form. You can add a record to the table with an SQL statement:

Dim strSQL as String

If IsNull(Dlookup("[Case]", "[Accident Investigation]",
"[Case]=Me.[Case]")) Then
strSQL = "INSERT INTO [Accident Investiongation] ([Task], [Due
Date]) VALUES ("""Accident Investigation on " & Me.Employee & """, &
DateAdd("d", 3, Me.AccidentDate) & ");"
Currentdb.Execute strSQL, dbFailOnError
End If
DoCmd.Close
 
Thanks for your help - I am extremely new at this and need it.

I am still getting the same error. I tried removing the SQL statement to
see if that would make a difference, but no luck.

The Case # is actually a text field, since it is a combination of letters
and numbers. Would that change anything?

BLW

Klatuu said:
Well, for starters, I don't see that you have populated either of the
varialbes for opening the form. stDocName is Dimmed but not populated.
stLinkCriteria is not even Dimmed which means you are not using Option
Explicit, which you really should be. But, you don't really even need to
open a form. You can add a record to the table with an SQL statement:

Dim strSQL as String

If IsNull(Dlookup("[Case]", "[Accident Investigation]",
"[Case]=Me.[Case]")) Then
strSQL = "INSERT INTO [Accident Investiongation] ([Task], [Due
Date]) VALUES ("""Accident Investigation on " & Me.Employee & """, &
DateAdd("d", 3, Me.AccidentDate) & ");"
Currentdb.Execute strSQL, dbFailOnError
End If
DoCmd.Close


--
Dave Hargis, Microsoft Access MVP


BLW said:
I have a form where I enter in accident information. When I save this form,
I want it to look into a another table (Accident Investigation) to see if an
investigation has been completed that matches the Case #. If no
investigation is found, I want it to add a task to the task list to remind
the user to complete the investigation.

Here is my code. What am I doing wrong? I keep getting "You canceled
Previous Operation"


Dim stDocName As String

If DCount("[Case]", "[Accident Investigation]", "[Case]=Me.[Case]") = 0
Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms(stDocName)![Task] = "Accident Investigation on " & Me.Employee
Forms(stDocName)![Due Date] = Me.Accident_Date + 3
DoCmd.Close
End If
DoCmd.Close
Click to expand...
Click to expand...
 
Yes. It was incorrect for either data type. Sorry I missed that earlier.
The data type to write for is the data type of the recordset field your are
referring to.
For text:
"[Case]= """ & Me.[Case] & """""

For numbers:
"[Case]= " & Me.[Case]



Dim strSQL as String

If IsNull(Dlookup("[Case]", "[Accident Investigation]", "[Case]= """ &
Me.[Case] & """"")) Then
strSQL = "INSERT INTO [Accident Investiongation] ([Task], [Due
Date]) VALUES ("""Accident Investigation on " & Me.Employee & """, &
DateAdd("d", 3, Me.AccidentDate) & ");"
Currentdb.Execute strSQL, dbFailOnError
End If
DoCmd.Close


--
Dave Hargis, Microsoft Access MVP


BLW said:
Thanks for your help - I am extremely new at this and need it.

I am still getting the same error. I tried removing the SQL statement to
see if that would make a difference, but no luck.

The Case # is actually a text field, since it is a combination of letters
and numbers. Would that change anything?

BLW

Klatuu said:
Well, for starters, I don't see that you have populated either of the
varialbes for opening the form. stDocName is Dimmed but not populated.
stLinkCriteria is not even Dimmed which means you are not using Option
Explicit, which you really should be. But, you don't really even need to
open a form. You can add a record to the table with an SQL statement:

Dim strSQL as String

If IsNull(Dlookup("[Case]", "[Accident Investigation]",
"[Case]=Me.[Case]")) Then
strSQL = "INSERT INTO [Accident Investiongation] ([Task], [Due
Date]) VALUES ("""Accident Investigation on " & Me.Employee & """, &
DateAdd("d", 3, Me.AccidentDate) & ");"
Currentdb.Execute strSQL, dbFailOnError
End If
DoCmd.Close


--
Dave Hargis, Microsoft Access MVP


BLW said:
I have a form where I enter in accident information. When I save this form,
I want it to look into a another table (Accident Investigation) to see if an
investigation has been completed that matches the Case #. If no
investigation is found, I want it to add a task to the task list to remind
the user to complete the investigation.

Here is my code. What am I doing wrong? I keep getting "You canceled
Previous Operation"


Dim stDocName As String

If DCount("[Case]", "[Accident Investigation]", "[Case]=Me.[Case]") = 0
Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms(stDocName)![Task] = "Accident Investigation on " & Me.Employee
Forms(stDocName)![Due Date] = Me.Accident_Date + 3
DoCmd.Close
End If
DoCmd.Close
Click to expand...
Click to expand...
Click to expand...
 
Works Beautifully, Thanks!!

Klatuu said:
Yes. It was incorrect for either data type. Sorry I missed that earlier.
The data type to write for is the data type of the recordset field your are
referring to.
For text:
"[Case]= """ & Me.[Case] & """""

For numbers:
"[Case]= " & Me.[Case]



Dim strSQL as String

If IsNull(Dlookup("[Case]", "[Accident Investigation]", "[Case]= """ &
Me.[Case] & """"")) Then
strSQL = "INSERT INTO [Accident Investiongation] ([Task], [Due
Date]) VALUES ("""Accident Investigation on " & Me.Employee & """, &
DateAdd("d", 3, Me.AccidentDate) & ");"
Currentdb.Execute strSQL, dbFailOnError
End If
DoCmd.Close


--
Dave Hargis, Microsoft Access MVP


BLW said:
Thanks for your help - I am extremely new at this and need it.

I am still getting the same error. I tried removing the SQL statement to
see if that would make a difference, but no luck.

The Case # is actually a text field, since it is a combination of letters
and numbers. Would that change anything?

BLW

Klatuu said:
Well, for starters, I don't see that you have populated either of the
varialbes for opening the form. stDocName is Dimmed but not populated.
stLinkCriteria is not even Dimmed which means you are not using Option
Explicit, which you really should be. But, you don't really even need to
open a form. You can add a record to the table with an SQL statement:

Dim strSQL as String

If IsNull(Dlookup("[Case]", "[Accident Investigation]",
"[Case]=Me.[Case]")) Then
strSQL = "INSERT INTO [Accident Investiongation] ([Task], [Due
Date]) VALUES ("""Accident Investigation on " & Me.Employee & """, &
DateAdd("d", 3, Me.AccidentDate) & ");"
Currentdb.Execute strSQL, dbFailOnError
End If
DoCmd.Close


--
Dave Hargis, Microsoft Access MVP


:

I have a form where I enter in accident information. When I save this form,
I want it to look into a another table (Accident Investigation) to see if an
investigation has been completed that matches the Case #. If no
investigation is found, I want it to add a task to the task list to remind
the user to complete the investigation.

Here is my code. What am I doing wrong? I keep getting "You canceled
Previous Operation"


Dim stDocName As String

If DCount("[Case]", "[Accident Investigation]", "[Case]=Me.[Case]") = 0
Then
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Forms(stDocName)![Task] = "Accident Investigation on " & Me.Employee
Forms(stDocName)![Due Date] = Me.Accident_Date + 3
DoCmd.Close
End If
DoCmd.Close
Click to expand...
Click to expand...
Click to expand...
 
Back
Top