A couple of problems

J

Joe Cilinceon

First I'm having 2 problems with some code on a button. The code is listed
at the bottom of this message. The first problem is the if then else in the
code. If the combo is empty I want it to put up a message that you must have
selected a unit before you can transfer. I've tried several ways now and it
just by passes that part of the statement and does the else part. The second
problem is how to write an SQL query to Update a table for a single unit.
Any help would be greatly appreciated.

If Me![cboVacantUnits] = Null Then 'This line is not working
I've even tried but runs from the Else regardless without problem
MsgBox "You Must Pick A Unit First"

Else
' Update Tenant's Credit for Possible Refund
[Credits] = [Credits] + Me.Refund

'Rent the New Unit
Dim strSQL1 As String
strSQL1 = ""
**** Here I need an Update Query with no idea how to make it work since I've
never been able to do it even with the wizard ****



'Update Rented Units Form
[fsubRentedUnits].Form![CustNo] = Null
[fsubRentedUnits].Form![EndDate] = Date
[fsubRentedUnits].Form![LastRate] = [fsubRentedUnits].Form![Rate]
[fsubRentedUnits].Form![Rate] = Null
[fsubRentedUnits].Form![Status] = 2
[fsubRentedUnits].Form![LastStartDate] =
[fsubRentedUnits].Form![StartDate]
[fsubRentedUnits].Form![StartDate] = Null

' Add a Note to Tenant's file
Dim strSQL2 As String

strSQL2 = "INSERT INTO tblTenantNotes ( CustNo, NoteDate, [Note] )"
& _
"SELECT '" & [CustNo] & "', Date() AS NoteDate, " & _
"'TRANSFERRED:From Unit #" & fsubRentedUnits.Form!Unit & _
" to Unit #" & [cboVacantUnits] & "' AS [Note];"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

End If

End Sub
 
D

Dan Artuso

Hi,
Since Null is an unknown state, nothing can be equal to it.
You have to use the IsNull function:
If IsNull(Me![cboVacantUnits]) = Null Then

For the second, well you just write an update statement the same as you have written
the Insert and then execute it.
The only thing is you have to know what the unique identifier is that will identitfy the
row you want to update. I can't tell what that is from your post, so something like:

strSql = "Update yourTable Set TheField = '" & Me.FieldWithValue & _
"' WHERE someField = " & Me.theIdentifierField

If the 'Value' is numerical, you don't need the sinlge quotes as delimiters.
If theIdentifierField is text, then you need to delimit with quotes.
 
J

Joe Cilinceon

Thanks Dan I'll give it a try. I have to update about 6 fields in the table.

--

Joe Cilinceon


Dan Artuso said:
Hi,
Since Null is an unknown state, nothing can be equal to it.
You have to use the IsNull function:
If IsNull(Me![cboVacantUnits]) = Null Then

For the second, well you just write an update statement the same as you have written
the Insert and then execute it.
The only thing is you have to know what the unique identifier is that will identitfy the
row you want to update. I can't tell what that is from your post, so something like:

strSql = "Update yourTable Set TheField = '" & Me.FieldWithValue & _
"' WHERE someField = " & Me.theIdentifierField

If the 'Value' is numerical, you don't need the sinlge quotes as delimiters.
If theIdentifierField is text, then you need to delimit with quotes.

--
HTH
Dan Artuso, Access MVP


First I'm having 2 problems with some code on a button. The code is listed
at the bottom of this message. The first problem is the if then else in the
code. If the combo is empty I want it to put up a message that you must have
selected a unit before you can transfer. I've tried several ways now and it
just by passes that part of the statement and does the else part. The second
problem is how to write an SQL query to Update a table for a single unit.
Any help would be greatly appreciated.

If Me![cboVacantUnits] = Null Then 'This line is not working
I've even tried but runs from the Else regardless without problem
MsgBox "You Must Pick A Unit First"

Else
' Update Tenant's Credit for Possible Refund
[Credits] = [Credits] + Me.Refund

'Rent the New Unit
Dim strSQL1 As String
strSQL1 = ""
**** Here I need an Update Query with no idea how to make it work since I've
never been able to do it even with the wizard ****



'Update Rented Units Form
[fsubRentedUnits].Form![CustNo] = Null
[fsubRentedUnits].Form![EndDate] = Date
[fsubRentedUnits].Form![LastRate] = [fsubRentedUnits].Form![Rate]
[fsubRentedUnits].Form![Rate] = Null
[fsubRentedUnits].Form![Status] = 2
[fsubRentedUnits].Form![LastStartDate] =
[fsubRentedUnits].Form![StartDate]
[fsubRentedUnits].Form![StartDate] = Null

' Add a Note to Tenant's file
Dim strSQL2 As String

strSQL2 = "INSERT INTO tblTenantNotes ( CustNo, NoteDate, [Note] )"
& _
"SELECT '" & [CustNo] & "', Date() AS NoteDate, " & _
"'TRANSFERRED:From Unit #" & fsubRentedUnits.Form!Unit & _
" to Unit #" & [cboVacantUnits] & "' AS [Note];"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

End If

End Sub
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Nulls just keep propogating.... < g >

I believe Dan meant
If IsNull(Me![cboVacantUnits]) = True Then

--

Ken Snell
<MS ACCESS MVP>

Dan Artuso said:
Hi,
Since Null is an unknown state, nothing can be equal to it.
You have to use the IsNull function:
If IsNull(Me![cboVacantUnits]) = Null Then

For the second, well you just write an update statement the same as you have written
the Insert and then execute it.
The only thing is you have to know what the unique identifier is that will identitfy the
row you want to update. I can't tell what that is from your post, so something like:

strSql = "Update yourTable Set TheField = '" & Me.FieldWithValue & _
"' WHERE someField = " & Me.theIdentifierField

If the 'Value' is numerical, you don't need the sinlge quotes as delimiters.
If theIdentifierField is text, then you need to delimit with quotes.

--
HTH
Dan Artuso, Access MVP


First I'm having 2 problems with some code on a button. The code is listed
at the bottom of this message. The first problem is the if then else in the
code. If the combo is empty I want it to put up a message that you must have
selected a unit before you can transfer. I've tried several ways now and it
just by passes that part of the statement and does the else part. The second
problem is how to write an SQL query to Update a table for a single unit.
Any help would be greatly appreciated.

If Me![cboVacantUnits] = Null Then 'This line is not working
I've even tried but runs from the Else regardless without problem
MsgBox "You Must Pick A Unit First"

Else
' Update Tenant's Credit for Possible Refund
[Credits] = [Credits] + Me.Refund

'Rent the New Unit
Dim strSQL1 As String
strSQL1 = ""
**** Here I need an Update Query with no idea how to make it work since I've
never been able to do it even with the wizard ****



'Update Rented Units Form
[fsubRentedUnits].Form![CustNo] = Null
[fsubRentedUnits].Form![EndDate] = Date
[fsubRentedUnits].Form![LastRate] = [fsubRentedUnits].Form![Rate]
[fsubRentedUnits].Form![Rate] = Null
[fsubRentedUnits].Form![Status] = 2
[fsubRentedUnits].Form![LastStartDate] =
[fsubRentedUnits].Form![StartDate]
[fsubRentedUnits].Form![StartDate] = Null

' Add a Note to Tenant's file
Dim strSQL2 As String

strSQL2 = "INSERT INTO tblTenantNotes ( CustNo, NoteDate, [Note] )"
& _
"SELECT '" & [CustNo] & "', Date() AS NoteDate, " & _
"'TRANSFERRED:From Unit #" & fsubRentedUnits.Form!Unit & _
" to Unit #" & [cboVacantUnits] & "' AS [Note];"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

End If

End Sub
Click to expand...
Click to expand...
 
D

Dan Artuso

Thanks Ken :)

--
HTH
Dan Artuso, Access MVP


Ken Snell said:
Nulls just keep propogating.... < g >

I believe Dan meant
If IsNull(Me![cboVacantUnits]) = True Then

--

Ken Snell
<MS ACCESS MVP>

Dan Artuso said:
Hi,
Since Null is an unknown state, nothing can be equal to it.
You have to use the IsNull function:
If IsNull(Me![cboVacantUnits]) = Null Then

For the second, well you just write an update statement the same as you have written
the Insert and then execute it.
The only thing is you have to know what the unique identifier is that will identitfy the
row you want to update. I can't tell what that is from your post, so something like:

strSql = "Update yourTable Set TheField = '" & Me.FieldWithValue & _
"' WHERE someField = " & Me.theIdentifierField

If the 'Value' is numerical, you don't need the sinlge quotes as delimiters.
If theIdentifierField is text, then you need to delimit with quotes.

--
HTH
Dan Artuso, Access MVP


First I'm having 2 problems with some code on a button. The code is listed
at the bottom of this message. The first problem is the if then else in the
code. If the combo is empty I want it to put up a message that you must have
selected a unit before you can transfer. I've tried several ways now and it
just by passes that part of the statement and does the else part. The second
problem is how to write an SQL query to Update a table for a single unit.
Any help would be greatly appreciated.

If Me![cboVacantUnits] = Null Then 'This line is not working
I've even tried but runs from the Else regardless without problem
MsgBox "You Must Pick A Unit First"

Else
' Update Tenant's Credit for Possible Refund
[Credits] = [Credits] + Me.Refund

'Rent the New Unit
Dim strSQL1 As String
strSQL1 = ""
**** Here I need an Update Query with no idea how to make it work since I've
never been able to do it even with the wizard ****



'Update Rented Units Form
[fsubRentedUnits].Form![CustNo] = Null
[fsubRentedUnits].Form![EndDate] = Date
[fsubRentedUnits].Form![LastRate] = [fsubRentedUnits].Form![Rate]
[fsubRentedUnits].Form![Rate] = Null
[fsubRentedUnits].Form![Status] = 2
[fsubRentedUnits].Form![LastStartDate] =
[fsubRentedUnits].Form![StartDate]
[fsubRentedUnits].Form![StartDate] = Null

' Add a Note to Tenant's file
Dim strSQL2 As String

strSQL2 = "INSERT INTO tblTenantNotes ( CustNo, NoteDate, [Note] )"
& _
"SELECT '" & [CustNo] & "', Date() AS NoteDate, " & _
"'TRANSFERRED:From Unit #" & fsubRentedUnits.Form!Unit & _
" to Unit #" & [cboVacantUnits] & "' AS [Note];"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

Joe Cilinceon

Actually it worked with

If IsNull(Me![cboVacantUnits]) Then

I also got the update query to also work perfectly for the first time ever.
Now, I'm back to the checkbox selection "payment screen" which I need to
isolate more than just the space number. I'll need to take a payment in bulk
(multiple spaces) but save each unit as if it was paid individually with a
new transaction number for each. I'll play with it for a while longer before
really asking for help though. Right now I'm thinking an array for the data
for the selected units.

--

Joe Cilinceon


Ken Snell said:
Nulls just keep propogating.... < g >

I believe Dan meant
If IsNull(Me![cboVacantUnits]) = True Then

--

Ken Snell
<MS ACCESS MVP>

Dan Artuso said:
Hi,
Since Null is an unknown state, nothing can be equal to it.
You have to use the IsNull function:
If IsNull(Me![cboVacantUnits]) = Null Then

For the second, well you just write an update statement the same as you have written
the Insert and then execute it.
The only thing is you have to know what the unique identifier is that
Click to expand...
will
identitfy the
row you want to update. I can't tell what that is from your post, so something like:

strSql = "Update yourTable Set TheField = '" & Me.FieldWithValue & _
"' WHERE someField = " & Me.theIdentifierField

If the 'Value' is numerical, you don't need the sinlge quotes as delimiters.
If theIdentifierField is text, then you need to delimit with quotes.
Click to expand...
in
the
Click to expand...
must
have
Click to expand...
and
it
just by passes that part of the statement and does the else part. The second
problem is how to write an SQL query to Update a table for a single unit.
Any help would be greatly appreciated.

If Me![cboVacantUnits] = Null Then 'This line is not working
I've even tried but runs from the Else regardless without problem
MsgBox "You Must Pick A Unit First"

Else
' Update Tenant's Credit for Possible Refund
[Credits] = [Credits] + Me.Refund

'Rent the New Unit
Dim strSQL1 As String
strSQL1 = ""
**** Here I need an Update Query with no idea how to make it work
Click to expand...
Click to expand...
since
I've
never been able to do it even with the wizard ****



'Update Rented Units Form
[fsubRentedUnits].Form![CustNo] = Null
[fsubRentedUnits].Form![EndDate] = Date
[fsubRentedUnits].Form![LastRate] = [fsubRentedUnits].Form![Rate]
[fsubRentedUnits].Form![Rate] = Null
[fsubRentedUnits].Form![Status] = 2
[fsubRentedUnits].Form![LastStartDate] =
[fsubRentedUnits].Form![StartDate]
[fsubRentedUnits].Form![StartDate] = Null

' Add a Note to Tenant's file
Dim strSQL2 As String

strSQL2 = "INSERT INTO tblTenantNotes ( CustNo, NoteDate, [Note] )"
& _
"SELECT '" & [CustNo] & "', Date() AS NoteDate, " & _
"'TRANSFERRED:From Unit #" &
Click to expand...
Click to expand...
fsubRentedUnits.Form!Unit
& _
" to Unit #" & [cboVacantUnits] & "' AS [Note];"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Your expression:
If IsNull(Me![cboVacantUnits]) Then

is the same as the other expression:
If IsNull(Me![cboVacantUnits]) = True Then

This is because the IsNull function returns either a True or False, and the
If statement looks for a True or False result from the expression; thus, the
= True part is not required in this situation. I always put it in, though,
so that the code is a bit easier to read.
--

Ken Snell
<MS ACCESS MVP>



Joe Cilinceon said:
Actually it worked with

If IsNull(Me![cboVacantUnits]) Then

I also got the update query to also work perfectly for the first time ever.
Now, I'm back to the checkbox selection "payment screen" which I need to
isolate more than just the space number. I'll need to take a payment in bulk
(multiple spaces) but save each unit as if it was paid individually with a
new transaction number for each. I'll play with it for a while longer before
really asking for help though. Right now I'm thinking an array for the data
for the selected units.

--

Joe Cilinceon


Ken Snell said:
Nulls just keep propogating.... < g >

I believe Dan meant
If IsNull(Me![cboVacantUnits]) = True Then

--

Ken Snell
<MS ACCESS MVP>

Dan Artuso said:
Hi,
Since Null is an unknown state, nothing can be equal to it.
You have to use the IsNull function:
If IsNull(Me![cboVacantUnits]) = Null Then

For the second, well you just write an update statement the same as
Click to expand...
you
have written
the Insert and then execute it.
The only thing is you have to know what the unique identifier is that
Click to expand...
will
identitfy the
row you want to update. I can't tell what that is from your post, so something like:

strSql = "Update yourTable Set TheField = '" & Me.FieldWithValue & _
"' WHERE someField = " & Me.theIdentifierField

If the 'Value' is numerical, you don't need the sinlge quotes as delimiters.
If theIdentifierField is text, then you need to delimit with quotes.

--
HTH
Dan Artuso, Access MVP


First I'm having 2 problems with some code on a button. The code is listed
at the bottom of this message. The first problem is the if then else
Click to expand...
in
the
code. If the combo is empty I want it to put up a message that you
Click to expand...
must
have
selected a unit before you can transfer. I've tried several ways now
Click to expand...
and
it
just by passes that part of the statement and does the else part.
Click to expand...
The
second
problem is how to write an SQL query to Update a table for a single unit.
Any help would be greatly appreciated.

If Me![cboVacantUnits] = Null Then 'This line is not working
I've even tried but runs from the Else regardless without problem
MsgBox "You Must Pick A Unit First"

Else
' Update Tenant's Credit for Possible Refund
[Credits] = [Credits] + Me.Refund

'Rent the New Unit
Dim strSQL1 As String
strSQL1 = ""
**** Here I need an Update Query with no idea how to make it work
Click to expand...
since
I've
never been able to do it even with the wizard ****



'Update Rented Units Form
[fsubRentedUnits].Form![CustNo] = Null
[fsubRentedUnits].Form![EndDate] = Date
[fsubRentedUnits].Form![LastRate] = [fsubRentedUnits].Form![Rate]
[fsubRentedUnits].Form![Rate] = Null
[fsubRentedUnits].Form![Status] = 2
[fsubRentedUnits].Form![LastStartDate] =
[fsubRentedUnits].Form![StartDate]
[fsubRentedUnits].Form![StartDate] = Null

' Add a Note to Tenant's file
Dim strSQL2 As String

strSQL2 = "INSERT INTO tblTenantNotes ( CustNo, NoteDate, [Note] )"
& _
"SELECT '" & [CustNo] & "', Date() AS NoteDate, " & _
"'TRANSFERRED:From Unit #" &
Click to expand...
fsubRentedUnits.Form!Unit
& _
" to Unit #" & [cboVacantUnits] & "' AS [Note];"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

End If

End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

Missing Operator 3
Syntax Error Need Help 3
Strange behavior and Error 2105 when oncurrent event cycles 0
Can't get rid of EOF message 2
Prevent foreign key autofill in linked subform 0
Problems with Delete Command 16

Top