Delete a record

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

Guest

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
 
Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True
 
Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

Ofer Cohen said:
Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


JOM said:
I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
 
Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


JOM said:
Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

Ofer Cohen said:
Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


JOM said:
I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

Ofer Cohen said:
Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


JOM said:
Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

Ofer Cohen said:
Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


JOM said:
Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

Ofer Cohen said:
Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


JOM said:
Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

Ofer Cohen said:
Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


JOM said:
Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

Ofer Cohen said:
Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


JOM said:
Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

Ofer Cohen said:
Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


JOM said:
Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


Ofer Cohen said:
Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


JOM said:
Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

Ofer Cohen said:
Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Can you post the full code?
--
HTH, Good Luck
BS"D


JOM said:
Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


Ofer Cohen said:
Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


JOM said:
Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Ofer I actually figured it out, I did twick sql statement but Am not sure how
I can use between txtbegindate and txtenddate

Private Sub cmdDeleteLN_Click()

If DCount("*", "[tblDescAccount]", "[LoanNumber] ='" & Me.[txtLoanNumber] &
"'") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] = #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] = #" & Me.[txtEndDate] & "#"
DoCmd.SetWarnings True
MsgBox "Record has been deleted"
Else
MsgBox "The Loan Number entered " & [txtLoanNumber] & " was not
found", vbInformation, "No such Loan Number"

End If


Ofer Cohen said:
Can you post the full code?
--
HTH, Good Luck
BS"D


JOM said:
Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


Ofer Cohen said:
Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


:

Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
You can try

DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] >= #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] <= #" & Me.[txtEndDate] & "#"

Or, you can try
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& " [LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& " And [EmployeeID] = " & Me.[cmbENames] & "" _
& " And [LoanDate] Between #" & Me.[txtBeginDate] & "#" _
& " And #" & Me.[txtEndDate] & "#"

--
HTH, Good Luck
BS"D


JOM said:
Ofer I actually figured it out, I did twick sql statement but Am not sure how
I can use between txtbegindate and txtenddate

Private Sub cmdDeleteLN_Click()

If DCount("*", "[tblDescAccount]", "[LoanNumber] ='" & Me.[txtLoanNumber] &
"'") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] = #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] = #" & Me.[txtEndDate] & "#"
DoCmd.SetWarnings True
MsgBox "Record has been deleted"
Else
MsgBox "The Loan Number entered " & [txtLoanNumber] & " was not
found", vbInformation, "No such Loan Number"

End If


Ofer Cohen said:
Can you post the full code?
--
HTH, Good Luck
BS"D


JOM said:
Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


:

Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


:

Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Perfect, that did the trick, thank you so much.....

Ofer Cohen said:
You can try

DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] >= #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] <= #" & Me.[txtEndDate] & "#"

Or, you can try
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& " [LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& " And [EmployeeID] = " & Me.[cmbENames] & "" _
& " And [LoanDate] Between #" & Me.[txtBeginDate] & "#" _
& " And #" & Me.[txtEndDate] & "#"

--
HTH, Good Luck
BS"D


JOM said:
Ofer I actually figured it out, I did twick sql statement but Am not sure how
I can use between txtbegindate and txtenddate

Private Sub cmdDeleteLN_Click()

If DCount("*", "[tblDescAccount]", "[LoanNumber] ='" & Me.[txtLoanNumber] &
"'") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] = #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] = #" & Me.[txtEndDate] & "#"
DoCmd.SetWarnings True
MsgBox "Record has been deleted"
Else
MsgBox "The Loan Number entered " & [txtLoanNumber] & " was not
found", vbInformation, "No such Loan Number"

End If


Ofer Cohen said:
Can you post the full code?
--
HTH, Good Luck
BS"D


:

Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


:

Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


:

Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Your welcome, have a great day, or evening

--
HTH, Good Luck
BS"D


JOM said:
Perfect, that did the trick, thank you so much.....

Ofer Cohen said:
You can try

DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] >= #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] <= #" & Me.[txtEndDate] & "#"

Or, you can try
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& " [LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& " And [EmployeeID] = " & Me.[cmbENames] & "" _
& " And [LoanDate] Between #" & Me.[txtBeginDate] & "#" _
& " And #" & Me.[txtEndDate] & "#"

--
HTH, Good Luck
BS"D


JOM said:
Ofer I actually figured it out, I did twick sql statement but Am not sure how
I can use between txtbegindate and txtenddate

Private Sub cmdDeleteLN_Click()

If DCount("*", "[tblDescAccount]", "[LoanNumber] ='" & Me.[txtLoanNumber] &
"'") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] = #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] = #" & Me.[txtEndDate] & "#"
DoCmd.SetWarnings True
MsgBox "Record has been deleted"
Else
MsgBox "The Loan Number entered " & [txtLoanNumber] & " was not
found", vbInformation, "No such Loan Number"

End If


:

Can you post the full code?
--
HTH, Good Luck
BS"D


:

Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


:

Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


:

Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Ofer, I have another request related to this string of message.....

my combobox that contains employee names has a row source of a union query,
as follows!
SELECT employee!EmplLastName & ", " & employee!EmplFirstName AS ENames,
employee.EmployeeID
FROM employee
UNION select '[*** All Employees ***]', -1 from employee ;
How will I add [*** All Employees ***] or -1 to the delete code...

This is just incase when one has no idea which employee entered the
information...

Ofer Cohen said:
Your welcome, have a great day, or evening

--
HTH, Good Luck
BS"D


JOM said:
Perfect, that did the trick, thank you so much.....

Ofer Cohen said:
You can try

DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] >= #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] <= #" & Me.[txtEndDate] & "#"

Or, you can try
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& " [LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& " And [EmployeeID] = " & Me.[cmbENames] & "" _
& " And [LoanDate] Between #" & Me.[txtBeginDate] & "#" _
& " And #" & Me.[txtEndDate] & "#"

--
HTH, Good Luck
BS"D


:

Ofer I actually figured it out, I did twick sql statement but Am not sure how
I can use between txtbegindate and txtenddate

Private Sub cmdDeleteLN_Click()

If DCount("*", "[tblDescAccount]", "[LoanNumber] ='" & Me.[txtLoanNumber] &
"'") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] = #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] = #" & Me.[txtEndDate] & "#"
DoCmd.SetWarnings True
MsgBox "Record has been deleted"
Else
MsgBox "The Loan Number entered " & [txtLoanNumber] & " was not
found", vbInformation, "No such Loan Number"

End If


:

Can you post the full code?
--
HTH, Good Luck
BS"D


:

Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


:

Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


:

Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
It quite risky to let the use delete all records in that time frame, it can
cause deletion of valuable information, so keep that in mind

You can try
If Me.[ComboName] = "*** All Employees ***" Then
Run here the code but remove the criteria with the Employee name
Else
Run here the previous code
End if
--
HTH, Good Luck
BS"D


JOM said:
Thanks Ofer, I have another request related to this string of message.....

my combobox that contains employee names has a row source of a union query,
as follows!
SELECT employee!EmplLastName & ", " & employee!EmplFirstName AS ENames,
employee.EmployeeID
FROM employee
UNION select '[*** All Employees ***]', -1 from employee ;
How will I add [*** All Employees ***] or -1 to the delete code...

This is just incase when one has no idea which employee entered the
information...

Ofer Cohen said:
Your welcome, have a great day, or evening

--
HTH, Good Luck
BS"D


JOM said:
Perfect, that did the trick, thank you so much.....

:

You can try

DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] >= #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] <= #" & Me.[txtEndDate] & "#"

Or, you can try
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& " [LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& " And [EmployeeID] = " & Me.[cmbENames] & "" _
& " And [LoanDate] Between #" & Me.[txtBeginDate] & "#" _
& " And #" & Me.[txtEndDate] & "#"

--
HTH, Good Luck
BS"D


:

Ofer I actually figured it out, I did twick sql statement but Am not sure how
I can use between txtbegindate and txtenddate

Private Sub cmdDeleteLN_Click()

If DCount("*", "[tblDescAccount]", "[LoanNumber] ='" & Me.[txtLoanNumber] &
"'") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE tblDescAccount.* FROM tblDescAccount WHERE" _
& "[LoanNumber] = '" & Me.[txtLoanNumber] & "'" _
& "And [EmployeeID] = " & Me.[cmbENames] & "" _
& "And [LoanDate] = #" & Me.[txtBeginDate] & "#" _
& "And [LoanDate] = #" & Me.[txtEndDate] & "#"
DoCmd.SetWarnings True
MsgBox "Record has been deleted"
Else
MsgBox "The Loan Number entered " & [txtLoanNumber] & " was not
found", vbInformation, "No such Loan Number"

End If


:

Can you post the full code?
--
HTH, Good Luck
BS"D


:

Ofer this will only delete where the loan number is equal to what I have
typed in the txtbox, including the records that I did not want to delete...
I added a combobox that contains empolyee names(cmbENames, and 2 text boxes
one for begin date(txtBeginDate) and the other for end date(txtEndDate), how
will code the delete code the delete statement to add those new textboxes


:

Check the data type of the fields, the text fields shouldn't single quote
before and after and a number should have none, so if the LoanNumber field
is number, then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = " & Me.[txtLoanNumber] & "
And [EmployeeID] = " & Me.[cmbENames].Column(1) & " And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then

Or if the EmployeeID field is text then try

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'
And [EmployeeID] = '" & Me.[cmbENames].Column(1) & "' And [LoanDate] = #" &
Me.[txtBeginDate] & "#") > 0 Then
--
HTH, Good Luck
BS"D


:

Ofer, I have tried, but am getting a datatype mismatch this is how my dlookup
looks like: (I did not add yet the between date yet)

If DCount("*", "[tblDesAcct]", "[LoanNumber] = '" & Me.[txtLoanNumber] & "'"
And _
"[EmployeeID] = " & Me.[cmbENames].Column(1) And _
"[LoanDate] = #" & Me.[txtBeginDate] & "#") > 0 Then

:

Not sure about the right names but it should be something like

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber] & "
And [TextField] = '" & Me.[TextField] & "' And [DateField] = #" &
Me.[DateField] & "#") > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

The same criteria should e for the delete statement



--
HTH, Good Luck
BS"D


:

Thanks ofer That worked perfect, I noticed that in my table, the LoanNumber
could be more than one, so I added a few more textboxes that would "filter"
the right information. I added a combobox that contains empolyee
names(cmbENames, and 2 text boxes one for begin date(txtBeginDate) and the
other for end date(txtEndDate), how will code the delete code to add those
new textboxes

:

Try

If DCount ("*", "[TableName]","[loan number] = " & Me.[txtLoannumber]) > 0
Then
Write delete code here
MsgBox "Record has deleted"
Else
MsgBox "No Reord found"
End If

--
HTH, Good Luck
BS"D


:

Thanks Ofer, that worked, I have another question on top of that, I would
like to inform the user that the records has been deleted if the record
already existed, or if the user entered a loannumber that did not exist, i
would like to inform the user that the loan number entered does not exixt,
how do I add that?

:

Try something like, but not before you back up your data base


Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = " &
Me.[txtLoannumber]
Docmd.SetWarnings True
========================
If the "loan number" field is text type then try
Docmd.SetWarnings False
Docmd.RunSQL "DELETE TableName.* FROM TableName WHERE [loan number] = '" &
Me.[txtLoannumber] & "'"
Docmd.SetWarnings True



--
HTH, Good Luck
BS"D


:

I have a unbound form, that contains unbound text box.
The txtLoanNumber is the unbound Text Box
there is a button that is be used to delete a record from the table
tblDetAccount based on the entry in txtLoanNumber

How do I code it so that it deletes the record where the loan number is
equal to txtLoannumber.
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unbound form question! 1
Parameter query based subform showing all records onload 1
Can I dynamically create a reference to a form ... 1
Report Question... 11
Search records by unbound combo box - please help! 5
Can't believe how out of touch I am with Access 3
DELETE FROM a table WHERE... 1
Go to a specific record on a form from the same form 3

Back
Top