Code not working correctly

[kateri4482]

I have the following code running from a command button, and it ignores the
section after the "&" sign in the strTableName statement, so it just gives me
a Table called tblBackupEnhancedCodes without the date. I can't figure out
what I am doing wrong. I want to run this periodically and create a backup
of a table but add the current date to the end of it.

Dim strTableName As String, strSQL As String

strTableName = "tblBaclupEnhancedCodes" & Format(Date, "ddmmmmyy")

strSQL = "Select tblEnhancedCodes.* Into " & strTableName & " From
tblEnhancedCodes;"

CurrentDB.Execute strSQL, dbFailOnError

I hope this makes sense and any assistance is appreciated.

 

[Marshall Barton]

I have the following code running from a command button, and it ignores the
section after the "&" sign in the strTableName statement, so it just gives me
a Table called tblBackupEnhancedCodes without the date. I can't figure out
what I am doing wrong. I want to run this periodically and create a backup
of a table but add the current date to the end of it.

Dim strTableName As String, strSQL As String

strTableName = "tblBaclupEnhancedCodes" & Format(Date, "ddmmmmyy")

strSQL = "Select tblEnhancedCodes.* Into " & strTableName & " From
tblEnhancedCodes;"

You probably have a text box named Date and its value is
Null.

If so, it's just another reminder not to use reserved words
when you make up names.

 

[kateri4482]

That is exactly the case. When I originally set up the table I had a field
called "Date" until I realized that is not a good idea, so I created a new
field but forgot to delete the old one. Thanks for the help!

 

[Marshall Barton]

You're welcome.

The reason I thought that might be the case is because of
all the time I wasted learning this lesson the hard way ;-)

 

[Dymondjack]

Lookup Reddicks Naming Conventions for guidelines on how to name things in
vba... thats where I got my foundation from and (as far as I know) its
basically the standard.

I don't have any links handy but its easily googlable.

 

[kateri4482]

Oops, I thought your reply was the reason it wasn't working and I answered
and implied that all was OK. However, I deleted the "Date" field I had but
now when I run this, it says it cannot find the Date field I specify. Aargh!
It has been a few days since I last accessed this post, so if it is too late
for you to see this and reply, I will create a new one in a few days.

Thanks again.

 

[Marshall Barton]

Technically, the word "field" refers to a column in a table
or query. If you are talking about a control (text box?) on
a form or report, then you should use the word "control".

The reason for the little lecture is because after deleting
the field from the table/query, you need to also check for a
control that is bound to your no longer existing date field.
Also check the report's Sorting and Grouping and its record
source query.

 

[mcescher]

Technically, the word "field" refers to a column in a table
or query.  If you are talking about a control (text box?) on
a form or report, then you should use the word "control".

The reason for the little lecture is because after deleting
the field from the table/query, you need to also check for a
control that is bound to your no longer existing date field.
Also check the report's Sorting and Grouping and its record
source query.
--
Marsh
MVP [MS Access]

Oops, I thought your reply was the reason it wasn't working and I answered
and implied that all was OK.  However, I deleted the "Date" field I had but
now when I run this, it says it cannot find the Date field I specify.  Aargh!

"Marshall Barton" wrote:

- Show quoted text -

He could explicitly call the date function with Format(Date(),
"ddmmmmyy")

Also, the OP may want to format the date with a "yyyymmdd" instead.
That will sort the archive tables chronologically.

Hope this helps,
Chris M.

 

[kateri4482]

Thanks Chris. I tried putting in the open and close parenthesis after the
word Date like you suggested, and they just disappear when I close and return
to MS Access. Any ideas?

Technically, the word "field" refers to a column in a table
or query. If you are talking about a control (text box?) on
a form or report, then you should use the word "control".

The reason for the little lecture is because after deleting
the field from the table/query, you need to also check for a
control that is bound to your no longer existing date field.
Also check the report's Sorting and Grouping and its record
source query.
--
Marsh
MVP [MS Access]

Oops, I thought your reply was the reason it wasn't working and I answered
and implied that all was OK. However, I deleted the "Date" field I had but
now when I run this, it says it cannot find the Date field I specify. Aargh!

"Marshall Barton" wrote:

kateri4482 wrote:

I have the following code running from a command button, and it ignores the
section after the "&" sign in the strTableName statement, so it just gives me
a Table called tblBackupEnhancedCodes without the date. I can't figure out
what I am doing wrong. I want to run this periodically and create a backup
of a table but add the current date to the end of it.

Dim strTableName As String, strSQL As String

strTableName = "tblBaclupEnhancedCodes" & Format(Date, "ddmmmmyy")

strSQL = "Select tblEnhancedCodes.* Into " & strTableName & " From
tblEnhancedCodes;"

You probably have a text box named Date and its value is
Null.

If so, it's just another reminder not to use reserved words
when you make up names.- Hide quoted text -

- Show quoted text -

He could explicitly call the date function with Format(Date(),
"ddmmmmyy")

Also, the OP may want to format the date with a "yyyymmdd" instead.
That will sort the archive tables chronologically.

Hope this helps,
Chris M.

 

[kateri4482]

Thanks Marshall. There are no controls that I can see on this form that are
bound to this now deleted field, and the source query just asks for all of
the fields. There is no report. I will keep searching for controls that may
indicate this field, but do you any other ideas in the meantime? Your
assistance is truly appreciated.

Technically, the word "field" refers to a column in a table
or query. If you are talking about a control (text box?) on
a form or report, then you should use the word "control".

The reason for the little lecture is because after deleting
the field from the table/query, you need to also check for a
control that is bound to your no longer existing date field.
Also check the report's Sorting and Grouping and its record
source query.
--
Marsh
MVP [MS Access]

Oops, I thought your reply was the reason it wasn't working and I answered
and implied that all was OK. However, I deleted the "Date" field I had but
now when I run this, it says it cannot find the Date field I specify. Aargh!

 

[Marshall Barton]

Thanks Marshall. There are no controls that I can see on this form that are
bound to this now deleted field, and the source query just asks for all of
the fields. There is no report. I will keep searching for controls that may
indicate this field, but do you any other ideas in the meantime? Your
assistance is truly appreciated.

Ah, um, sorry, don't where the report thought came from.

In a form, a parameter prompt popup can only come from the
form's record source query. Inspect the query carefully,
especially the Where clause. If the query is based on
another query, then check that query too.

 

[kateri4482]

Thanks again Marshall. I do not have a parameter prompt popup in the query.
Here is the query that runs the form:

SELECT TblEnhancedCodes.*
FROM TblEnhancedCodes
WHERE (((TblEnhancedCodes.Archive)=No))
ORDER BY TblEnhancedCodes.Code;

And there is now no Date field in the table. But I still get the error that
the "Date" field cannot be found when I try and execute this:

Dim strTableName As String, strSQL As String

strTableName = "tblBackupEnhancedCodes" & Format(Date, "mmmmyyyy")
strSQL = "Select tblEnhancedCodes.* Into " & strTableName & " From
tblEnhancedCodes;"

This is really frustrating.

 

[kateri4482]

Yep, that is exactly what I want. I performed a compact and repair, and I DO
have the statement the same as what you indicate. And I still get the error
that the "Date" field cannot be found. Here is what I have:

Dim strTableName As String, strSQL As String

strTableName = "tblBackupEnhancedCodes" & Format(Date, "mmmmyyyy")
strSQL = "Select tblEnhancedCodes.* Into " & strTableName & " From
tblEnhancedCodes;"

And THANKS for jumping in. I am frustrated and will take any and all
suggestions that I can.

 

[kateri4482]

OK - this is the weirdest thing. When I create a command button on another
form and run the same statement, it works! So there is something seriously
wrong with this form that is keeping me from running it. I have combed
through this form and everything associated with it, and can't find a single
thing that is wrong. Thanks for everyone's help, and if you have any other
suggestions on where I might find my mistake, that would be great.
Otherwise, I will run it from another form. Your kindness and patience is
appreciated.

 

[Marshall Barton]

OK - this is the weirdest thing. When I create a command button on another
form and run the same statement, it works! So there is something seriously
wrong with this form that is keeping me from running it. I have combed
through this form and everything associated with it, and can't find a single
thing that is wrong. Thanks for everyone's help, and if you have any other
suggestions on where I might find my mistake, that would be great.
Otherwise, I will run it from another form. Your kindness and patience is
appreciated.

This is starting to sound like the form (or its code module)
is corrupted. Before doing anything else, make a backup of
your database so you can get back to the current state if
things get worse.

If this a corruption issue, there is no guaranteed way to
clean it up. My guess is that the problem is in the code
module. If so, then Decompile the database as described
here http://allenbrowne.com/recover.html
If that clears it up, then the most likely cause of the
corruption is that you were editing the form's code while
the form was still running. A good practice is to *always*
switch back to design view before making *any* change to a
form/report module.

If the corruption is in the form itself and not in its
module, try renaming the form and then Copy/Paste the form
back to its original name. Doing a Compact/Repair of your
database might help, but probably not in this case. More
likely to succeed is creating a new, blank database, setting
all its options as needed (especially Name Auto Correct to
NO) and then import everything from your problem database.
If this still doesn't clear it up, then start over and
import everything except the problem form and recreate the
form from scratch.