A couple of problems

[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

 

[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.

 

[Joe Cilinceon]

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

--

Joe Cilinceon

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

 

[Ken Snell [MVP]]

Nulls just keep propogating.... < g >

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

--

Ken Snell
<MS ACCESS MVP>

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

 

[Dan Artuso]

Thanks Ken

--
HTH
Dan Artuso, Access MVP

Nulls just keep propogating.... < g >

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

--

Ken Snell
<MS ACCESS MVP>

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

 

[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

Nulls just keep propogating.... < g >

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

--

Ken Snell
<MS ACCESS MVP>

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.

in

the

must

have

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

 

[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>

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

Nulls just keep propogating.... < g >

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

--

Ken Snell
<MS ACCESS MVP>

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