Findfirst problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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 & """"
 
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

Allen Browne said:
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.

PeterW said:
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
Click to expand...
Click to expand...
 
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!)



PeterW said:
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

Allen Browne said:
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.

PeterW said:
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
Click to expand...
Click to expand...
Click to expand...
 
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?

Douglas J. Steele said:
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!)



PeterW said:
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

Allen Browne said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

PeterW said:
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?

Douglas J. Steele said:
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!)



PeterW said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top