Findfirst problem

[Guest]

I am trying to design an database that allocates standard tasks to a job. I
have a table called tasks and am using a command button on a form to create
records in this table after the user has entered a job no. Creating the tasks
works ok but I want to stop the tasks from duplicating if anyone presses the
button again. I have tried the following code but it won't work the programme
always seems to find the first record in the table and use this value. If I
substitute the Me.JobNo for an actual value in the table it does work - very
grateful for any suggestions

code currently is
Private Sub btnCreateTasks_Click()
'add records to tasks table
Dim dbJobs As DAO.Database
Dim rsJobs As DAO.Recordset
Dim txtJobNo As String
Dim strFind As String

Set dbJobs = CurrentDb
Set rsJobs = dbJobs.OpenRecordset("tblTasks", dbOpenDynaset)
txtJobNo = Me.JobNo

rsJobs.FindFirst "JobNo = 'Me.JobNo'"

If rsJobs.NoMatch = True Then
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Order long delivery items"
rsJobs![CompDate] = Me.FitDate - 21
rsJobs.Update
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Send Painter Schedule"
rsJobs![CompDate] = Me.FitDate - 7
rsJobs.Update
Else
MsgBox "The tasks are already assigned"
End If
End Sub

 

[Allen Browne]

Concatenate the value of the text box on your form into the Criteria string
for FindFirst:
rsJobs.FindFirst "JobNo = " & Me.JobNo

If JobNo is a Text field (not a Number field), add extra quotes:
rsJobs.FindFirst "JobNo = """ & Me.JobNo & """"

 

[Guest]

Thanks Allen
Me.JobNo is a text field and I have tried the code you suggested by adding
extra quotes but I get an expected end of statement error message with the
final 4 " highlighted - can you tell me what I'm missing please
thanks in anticipation

Concatenate the value of the text box on your form into the Criteria string
for FindFirst:
rsJobs.FindFirst "JobNo = " & Me.JobNo

If JobNo is a Text field (not a Number field), add extra quotes:
rsJobs.FindFirst "JobNo = """ & Me.JobNo & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps.org.

I am trying to design an database that allocates standard tasks to a job. I
have a table called tasks and am using a command button on a form to
create
records in this table after the user has entered a job no. Creating the
tasks
works ok but I want to stop the tasks from duplicating if anyone presses
the
button again. I have tried the following code but it won't work the
programme
always seems to find the first record in the table and use this value. If
I
substitute the Me.JobNo for an actual value in the table it does work -
very
grateful for any suggestions

code currently is
Private Sub btnCreateTasks_Click()
'add records to tasks table
Dim dbJobs As DAO.Database
Dim rsJobs As DAO.Recordset
Dim txtJobNo As String
Dim strFind As String

Set dbJobs = CurrentDb
Set rsJobs = dbJobs.OpenRecordset("tblTasks", dbOpenDynaset)
txtJobNo = Me.JobNo

rsJobs.FindFirst "JobNo = 'Me.JobNo'"

If rsJobs.NoMatch = True Then
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Order long delivery items"
rsJobs![CompDate] = Me.FitDate - 21
rsJobs.Update
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Send Painter Schedule"
rsJobs![CompDate] = Me.FitDate - 7
rsJobs.Update
Else
MsgBox "The tasks are already assigned"
End If
End Sub

 

[Douglas J. Steele]

Assuming you put one quote at the beginning, three in the middle and four at
the end, that should work.

Other alternatives are:

rsJobs.FindFirst "JobNo = " & Chr$(34) & Me.JobNo & Chr$(34)

or

rsJobs.FindFirst "JobNo = '" & Me.JobNo & "'"

where, exagerated for clarity, that's:

rsJobs.FindFirst "JobNo = ' " & Me.JobNo & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Allen
Me.JobNo is a text field and I have tried the code you suggested by adding
extra quotes but I get an expected end of statement error message with the
final 4 " highlighted - can you tell me what I'm missing please
thanks in anticipation

Concatenate the value of the text box on your form into the Criteria
string
for FindFirst:
rsJobs.FindFirst "JobNo = " & Me.JobNo

If JobNo is a Text field (not a Number field), add extra quotes:
rsJobs.FindFirst "JobNo = """ & Me.JobNo & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps.org.

I am trying to design an database that allocates standard tasks to a
job. I
have a table called tasks and am using a command button on a form to
create
records in this table after the user has entered a job no. Creating the
tasks
works ok but I want to stop the tasks from duplicating if anyone
presses
the
button again. I have tried the following code but it won't work the
programme
always seems to find the first record in the table and use this value.
If
I
substitute the Me.JobNo for an actual value in the table it does work -
very
grateful for any suggestions

code currently is
Private Sub btnCreateTasks_Click()
'add records to tasks table
Dim dbJobs As DAO.Database
Dim rsJobs As DAO.Recordset
Dim txtJobNo As String
Dim strFind As String

Set dbJobs = CurrentDb
Set rsJobs = dbJobs.OpenRecordset("tblTasks", dbOpenDynaset)
txtJobNo = Me.JobNo

rsJobs.FindFirst "JobNo = 'Me.JobNo'"

If rsJobs.NoMatch = True Then
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Order long delivery items"
rsJobs![CompDate] = Me.FitDate - 21
rsJobs.Update
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Send Painter Schedule"
rsJobs![CompDate] = Me.FitDate - 7
rsJobs.Update
Else
MsgBox "The tasks are already assigned"
End If
End Sub

 

[Guest]

Thanks again
I have tried 1, 3 and 4 quotes and always get the same error message -
however I have got over this by using the strFind variable to concatenate the
string and then use FindFirst strFind - unfortunately this has now stopped
the code updating table when it reaches the first update I get a run time
error 3201 because there is no related record in tblJob - is this something
straightforward?

Assuming you put one quote at the beginning, three in the middle and four at
the end, that should work.

Other alternatives are:

rsJobs.FindFirst "JobNo = " & Chr$(34) & Me.JobNo & Chr$(34)

or

rsJobs.FindFirst "JobNo = '" & Me.JobNo & "'"

where, exagerated for clarity, that's:

rsJobs.FindFirst "JobNo = ' " & Me.JobNo & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Allen
Me.JobNo is a text field and I have tried the code you suggested by adding
extra quotes but I get an expected end of statement error message with the
final 4 " highlighted - can you tell me what I'm missing please
thanks in anticipation

Concatenate the value of the text box on your form into the Criteria
string
for FindFirst:
rsJobs.FindFirst "JobNo = " & Me.JobNo

If JobNo is a Text field (not a Number field), add extra quotes:
rsJobs.FindFirst "JobNo = """ & Me.JobNo & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps.org.

I am trying to design an database that allocates standard tasks to a
job. I
have a table called tasks and am using a command button on a form to
create
records in this table after the user has entered a job no. Creating the
tasks
works ok but I want to stop the tasks from duplicating if anyone
presses
the
button again. I have tried the following code but it won't work the
programme
always seems to find the first record in the table and use this value.
If
I
substitute the Me.JobNo for an actual value in the table it does work -
very
grateful for any suggestions

code currently is
Private Sub btnCreateTasks_Click()
'add records to tasks table
Dim dbJobs As DAO.Database
Dim rsJobs As DAO.Recordset
Dim txtJobNo As String
Dim strFind As String

Set dbJobs = CurrentDb
Set rsJobs = dbJobs.OpenRecordset("tblTasks", dbOpenDynaset)
txtJobNo = Me.JobNo

rsJobs.FindFirst "JobNo = 'Me.JobNo'"

If rsJobs.NoMatch = True Then
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Order long delivery items"
rsJobs![CompDate] = Me.FitDate - 21
rsJobs.Update
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Send Painter Schedule"
rsJobs![CompDate] = Me.FitDate - 7
rsJobs.Update
Else
MsgBox "The tasks are already assigned"
End If
End Sub

 

[Guest]

Sorry to waste peoples time I realised it was as simple as saving the record
before running the update - at least this seems to make it work ok
Thamks for all the help

Thanks again
I have tried 1, 3 and 4 quotes and always get the same error message -
however I have got over this by using the strFind variable to concatenate the
string and then use FindFirst strFind - unfortunately this has now stopped
the code updating table when it reaches the first update I get a run time
error 3201 because there is no related record in tblJob - is this something
straightforward?

Assuming you put one quote at the beginning, three in the middle and four at
the end, that should work.

Other alternatives are:

rsJobs.FindFirst "JobNo = " & Chr$(34) & Me.JobNo & Chr$(34)

or

rsJobs.FindFirst "JobNo = '" & Me.JobNo & "'"

where, exagerated for clarity, that's:

rsJobs.FindFirst "JobNo = ' " & Me.JobNo & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks Allen
Me.JobNo is a text field and I have tried the code you suggested by adding
extra quotes but I get an expected end of statement error message with the
final 4 " highlighted - can you tell me what I'm missing please
thanks in anticipation

:

Concatenate the value of the text box on your form into the Criteria
string
for FindFirst:
rsJobs.FindFirst "JobNo = " & Me.JobNo

If JobNo is a Text field (not a Number field), add extra quotes:
rsJobs.FindFirst "JobNo = """ & Me.JobNo & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps.org.

I am trying to design an database that allocates standard tasks to a
job. I
have a table called tasks and am using a command button on a form to
create
records in this table after the user has entered a job no. Creating the
tasks
works ok but I want to stop the tasks from duplicating if anyone
presses
the
button again. I have tried the following code but it won't work the
programme
always seems to find the first record in the table and use this value.
If
I
substitute the Me.JobNo for an actual value in the table it does work -
very
grateful for any suggestions

code currently is
Private Sub btnCreateTasks_Click()
'add records to tasks table
Dim dbJobs As DAO.Database
Dim rsJobs As DAO.Recordset
Dim txtJobNo As String
Dim strFind As String

Set dbJobs = CurrentDb
Set rsJobs = dbJobs.OpenRecordset("tblTasks", dbOpenDynaset)
txtJobNo = Me.JobNo

rsJobs.FindFirst "JobNo = 'Me.JobNo'"

If rsJobs.NoMatch = True Then
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Order long delivery items"
rsJobs![CompDate] = Me.FitDate - 21
rsJobs.Update
rsJobs.AddNew
rsJobs![TaskID] = Nz(DMax("[TaskID]", "tblTasks"), 0) + 1
rsJobs![JobNo] = Me.JobNo
rsJobs![TaskDescrp] = "Send Painter Schedule"
rsJobs![CompDate] = Me.FitDate - 7
rsJobs.Update
Else
MsgBox "The tasks are already assigned"
End If
End Sub