Problem saving new record & "invalid use of null"

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

Guest

I have a table [T_LocTypes] with 3 fields:

* [LocTypeCode] - AutoNumber Primary Key
* [LocActivityType] - Byte (1 of 54 types of organizations)
* [LocType] - String/50 - things like "Fire Station", "Water Treatment
Plant", "Jail"

I have a form [F_NewLocTypes]. Two controls on the form are:

* [ACTIVITY_CODE] - one value for organization type chosen in a list box on
an earlier form
* [txtNewLocType] - String entered by user describing main use of location -
"Office", any of the LocType types.

I have a button on the form [btnSaveNewLocType] that has this On_Click
procedure (not showing un-involved code):
_______________________
Private Sub btnSaveNewLocType_Click()
Dim strSQLInsert As String

strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType)
SELECT [Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE],
[Forms]![FYbf_Locations_NewType_3].[txtNewFacilityType]"

DoCmd.RunSQL strSQLInsert

End Sub
__________________
After I enter a string in [txtNewFacilityType] I click the Save button. This
saves a new record - but there is no value in [LocType] - there is a value in
[LocActivityType].

I added this code after the "Dim strSQLInsert As String" statement to try to
find out what's going on:
____________________________
Dim LocActivityType As Byte
Dim LocType As String * 50

LocActivityType = Forms!FYbf_Locations_NewType_3!ACTIVITY_CODE
Debug.Print LocActivityType

LocType = Forms!FYbf_Locations_NewType_3!txtNewFacilityType
Debug.Print LocType
____________________
I see the correct [LocActivityType] value show up in the Immediate Window,
but when I get to the "LocType = ...!txtNewFacilityType" line I get an
"Invalid Use of Null Statement".

But there is a string entered in that control on the form.

Anyone see how I'm shooting myself in the foot?

TIA - John D
 
Try using the alternative syntax for appending a record.

Dim strSQLInsert As String

strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType)" & _
" Values(" & [Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE] & _
", """ & [Forms]![FYbf_Locations_NewType_3].[txtNewFacilityType] & _
""" )""

DoCmd.RunSQL strSQLInsert



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
John - I copied your code exactly - but get a "missing semicolon at end of
statement" error. I put asemicolon in at the end of string - got same error
message. Moved it one space forward in string - got Syntax error. One more
forward to just after the ) - Characters Found at End of SQL error. I don't
imagine it would go inside the ).

I converted your code into one long line with no - ' " ( ) ; -
in the string except for one " at beginning and one at end. Got Syntax Error.
BUT when I replaced VALUE with SELECT with nothing else changed I could save
a record, but again with no value in [LocType].

Any thougnts? Thnx John D








John Spencer said:
Try using the alternative syntax for appending a record.

Dim strSQLInsert As String

strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType)" & _
" Values(" & [Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE] & _
", """ & [Forms]![FYbf_Locations_NewType_3].[txtNewFacilityType] & _
""" )""

DoCmd.RunSQL strSQLInsert
Click to expand...
 
Try fixing the references to the controls on the form and try removing the
extra quote mark at the end. I shouldn't construct code late at night.

I think the following is correct
strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType) Values("
& [Forms]![FYbf_Locations_NewType_3]![ACTIVITY_CODE] & ", """ &
[Forms]![FYbf_Locations_NewType_3]![txtNewFacilityType] & """ );"



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John D said:
John - I copied your code exactly - but get a "missing semicolon at end of
statement" error. I put asemicolon in at the end of string - got same
error
message. Moved it one space forward in string - got Syntax error. One more
forward to just after the ) - Characters Found at End of SQL error. I
don't
imagine it would go inside the ).

I converted your code into one long line with no - ' " ( )
-
in the string except for one " at beginning and one at end. Got Syntax
Error.
BUT when I replaced VALUE with SELECT with nothing else changed I could
save
a record, but again with no value in [LocType].

Any thougnts? Thnx John D








John Spencer said:
Try using the alternative syntax for appending a record.

Dim strSQLInsert As String

strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType)" & _
" Values(" & [Forms]![FYbf_Locations_NewType_3].[ACTIVITY_CODE] & _
", """ & [Forms]![FYbf_Locations_NewType_3].[txtNewFacilityType] & _
""" )""

DoCmd.RunSQL strSQLInsert
Click to expand...
Click to expand...
 
"John Spencer" wrote:
Alas - same result - it saves a record but without any data appearing in
[TYbf_LocType].[LocType]!

The field [FYbf_Locaitons_NewType_3]![txtNewFacilityType] is NOT a bound
control - but I don't think that's the issue. It's an unbound text box - not
tied to the field into which I want the value inserted. However, the INSERT
INTO statement should work even so - I assume.

As usual, probably something obvious - but I can't see it. Any suggestions?
Thanks - John D
 
IF [Forms]![FYbf_Locations_NewType_3]![txtNewFacilityType] is null then that
would cause an error.

the Query string then would have to read
INSERT INTO TYbf_LocType(LocActivityType, LocType)
Values(123,Null)
Instead of
INSERT INTO TYbf_LocType(LocActivityType, LocType)
Values(123,"")

Could that be the problem? If so, you are going to have to test the value
of txtNewFaciltityType and then build your string depending on whether or
not txtNewFaciltityType is Null, a zero-length string, or a string with at
least one character.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

John D said:
:
Alas - same result - it saves a record but without any data appearing in
[TYbf_LocType].[LocType]!

The field [FYbf_Locaitons_NewType_3]![txtNewFacilityType] is NOT a bound
control - but I don't think that's the issue. It's an unbound text box -
not
tied to the field into which I want the value inserted. However, the
INSERT
INTO statement should work even so - I assume.

As usual, probably something obvious - but I can't see it. Any
suggestions?
Thanks - John D


Try fixing the references to the controls on the form and try removing
the
extra quote mark at the end. I shouldn't construct code late at night.

I think the following is correct
strSQLInsert = "INSERT INTO TYbf_LocType(LocActivityType, LocType)
Values("
& [Forms]![FYbf_Locations_NewType_3]![ACTIVITY_CODE] & ", """ &
[Forms]![FYbf_Locations_NewType_3]![txtNewFacilityType] & """ );"
Click to expand...
Click to expand...
 
Note - this is the 3rd time in this series of postings that I've typed in a
reply and when I click Post the server's too busy and my entire message is
lost! Bummer!!!


VOILA!

Had one of those "Oh my Gosh, could it be ...?" Moments.

Once the syntax was correct (is syntax like a tax on booze?) I kept getting
the Null error. But how could that be? I can see the text in the control
right there on the screen. AUUGHH!!!

Then - the "I wonder ..." flash. Before I had been entering text in the
control, then clicking the Save Button - and getting the Null Error. So -
that means VBA can't "see" what I can "see" - right? Why not? Because somehow
the data isn't in a position for VBA to see it? Duh.

So - I hit the Enter Key after typing in the text and before clicking the
Save Button.

VOILA!!! It worked.

So - text just "floating" in a control's space isn't text VBA can see? Right?

If so - how can I "commit" the data without having the user hit the Enter
Key so VBA can see it?

Thanks - John D
 
Ever here the Ham and Eggs for Breakfast joke?

Before I hit the Enter Key the data was like the Chicken - only involved
with Breakfast. When I hit the Enter Key, the data became the Pig - really
committed.
 
Back
Top