Prev: Allen Browne's Recurring events
Next: Help with dCount - “Data Type mismatch in criteria expression”
From: KSmith on 25 May 2010 09:54
The section of code below is causing me problems.

Private Sub AddBadParts()

Dim dbs As DAO.Database
Dim strSQL As String
Dim MyDate
Dim MyTime

Set dbs = CurrentDb

'strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (" &
Me.[txtCatNum] & ");"
strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (""Hello
101st Try"");"

dbs.Execute strSQL

End Sub

ID - is the first field in the table (auto number)
CatalogNumber - is the second field in the table (text)

txtCatNum is a TextBox on the form that displays the data.

The strSQL line above that I have " ' " commented out is the line that gives
me the Error 2465 Can't find the field "|" in my expression.

The strSQL line below that one works. It puts the " Hello" statement in the
CatalogNumber Field.

Thanks in advance for all of your help.
--
KSmith
From: KSmith on 25 May 2010 10:29
Never Mind. I found my problem.

Thanks
--
KSmith


"KSmith" wrote:

> The section of code below is causing me problems.
>
> Private Sub AddBadParts()
>
> Dim dbs As DAO.Database
> Dim strSQL As String
> Dim MyDate
> Dim MyTime
>
> Set dbs = CurrentDb
>
> 'strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (" &
> Me.[txtCatNum] & ");"
> strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (""Hello
> 101st Try"");"
>
> dbs.Execute strSQL
>
> End Sub
>
> ID - is the first field in the table (auto number)
> CatalogNumber - is the second field in the table (text)
>
> txtCatNum is a TextBox on the form that displays the data.
>
> The strSQL line above that I have " ' " commented out is the line that gives
> me the Error 2465 Can't find the field "|" in my expression.
>
> The strSQL line below that one works. It puts the " Hello" statement in the
> CatalogNumber Field.
>
> Thanks in advance for all of your help.
> --
> KSmith
From: Marshall Barton on 25 May 2010 09:37
KSmith wrote:

>The section of code below is causing me problems.
>
>Private Sub AddBadParts()
>
> Dim dbs As DAO.Database
> Dim strSQL As String
> Dim MyDate
> Dim MyTime
>
> Set dbs = CurrentDb
>
> 'strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (" &
>Me.[txtCatNum] & ");"
> strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (""Hello
>101st Try"");"
>
> dbs.Execute strSQL
>
>End Sub
>
>ID - is the first field in the table (auto number)
>CatalogNumber - is the second field in the table (text)
>
>txtCatNum is a TextBox on the form that displays the data.
>
>The strSQL line above that I have " ' " commented out is the line that gives
>me the Error 2465 Can't find the field "|" in my expression.
>
>The strSQL line below that one works. It puts the " Hello" statement in the
>CatalogNumber Field.
>

Because CatalogNumber is a text field, you must put the
value in quotes:

strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES ("""
& Me.[txtCatNum] & """)"

Are you sure txtCatNum actually has something in it when the
code runs?

--
Marsh
MVP [MS Access]
From: KSmith on 25 May 2010 14:12
I'm still having trouble with this form. The form that I copied from works
great.
It's like I get one thing to working and something else screws up. Is there
an in-correct way to copy a form?

Here is what I done to get where I am today.

I clicked on 'without opening' the 1st form.
Pressed 'Ctrl C'
Pressed 'Ctrl V'
And re-named the form in the dialog box.

Changed the Record Source Line of the 2nd form.
Renamed the text boxes of the ones that changed, some stayed the same.
Most of the text boxes are bound to a field in the two main tables.

This is crazy. I don't understand why it works in one form but not in
another.

Is there a 'special way' that I need to be calling a text box from the form?

Thanks for the help.
--
KSmith


"Marshall Barton" wrote:

> KSmith wrote:
>
> >The section of code below is causing me problems.
> >
> >Private Sub AddBadParts()
> >
> > Dim dbs As DAO.Database
> > Dim strSQL As String
> > Dim MyDate
> > Dim MyTime
> >
> > Set dbs = CurrentDb
> >
> > 'strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (" &
> >Me.[txtCatNum] & ");"
> > strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES (""Hello
> >101st Try"");"
> >
> > dbs.Execute strSQL
> >
> >End Sub
> >
> >ID - is the first field in the table (auto number)
> >CatalogNumber - is the second field in the table (text)
> >
> >txtCatNum is a TextBox on the form that displays the data.
> >
> >The strSQL line above that I have " ' " commented out is the line that gives
> >me the Error 2465 Can't find the field "|" in my expression.
> >
> >The strSQL line below that one works. It puts the " Hello" statement in the
> >CatalogNumber Field.
> >
>
> Because CatalogNumber is a text field, you must put the
> value in quotes:
>
> strSQL = "INSERT INTO tblTally ([CatalogNumber]) VALUES ("""
> & Me.[txtCatNum] & """)"
>
> Are you sure txtCatNum actually has something in it when the
> code runs?
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: Marshall Barton on 25 May 2010 14:26
KSmith wrote:

>I'm still having trouble with this form. The form that I copied from works
>great.
>It's like I get one thing to working and something else screws up. Is there
>an in-correct way to copy a form?
>
>Here is what I done to get where I am today.
>
>I clicked on 'without opening' the 1st form.
>Pressed 'Ctrl C'
>Pressed 'Ctrl V'
>And re-named the form in the dialog box.
>
>Changed the Record Source Line of the 2nd form.
>Renamed the text boxes of the ones that changed, some stayed the same.
>Most of the text boxes are bound to a field in the two main tables.
>
>This is crazy. I don't understand why it works in one form but not in
>another.
>
>Is there a 'special way' that I need to be calling a text box from the form?


That's the standard way to copy an object in the db window.

If you change the name of something, then you need to find
and change everything that referred to the something.

If you changed the record source query, make sure that all
the fields used in bound controls are correct and still in
the query.

Did you copy the constructed query into a new query's SQL
view and verify that the query works as expected?

I don't see why you felt the need to change the name of any
control.

--
Marsh
MVP [MS Access]
 |  Next  |  Last
Pages: 1 2
Prev: Allen Browne's Recurring events
Next: Help with dCount - “Data Type mismatch in criteria expression”