Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Save number entered

I have an unbound field where I will be entering zip codes. The name is txtZipEntered.

Next to it is a button. When you hit the button it runs a report based on the zip code entered in txtZipEntered. I would like to enter code for the button that will save the zip code and a time stamp on a table. The table is called tblZipCodeHistory. This has two columns ZipEntered and TimeStamp.

Every time the button is hit, I want the table to be updated with a "ZipEntered" and a time stamp of when it happened.

Any idea the code of this?
0
cansevin
Asked:
cansevin
  • 16
  • 14
  • 5
  • +1
1 Solution
 
Rey Obrero (Capricorn1)Commented:
to save the info to the table, call this in the click event of the button

currentdb.execute "insert into  tblZipCodeHistory(ZipEntered, TimeStamp) values ('" & me.txtZipEntered & "', Now())
0
 
ste5anSenior DeveloperCommented:
E.g.

Dim Statement As String

Statement = "INSERT INTO tblZipCodeHistory (ZipEntered, TimeStamp ) VALUES ( '{0}', {1} ); "
Statement = Replace(Statement, "{0}", txtZipEntered.Value)
Statement = Replace(Statement, "{1}", Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#"))
CurrentDb.Execute Statement, dbFailOnError Or dbSeeChanges

Open in new window

0
 
cansevinAuthor Commented:
Ste4an

I am trying to use your code... there is an error and it highlights the last line. Not sure what that last line means or how to fix it. Any ideas?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Rey Obrero (Capricorn1)Commented:
@cansevin


did   you try the code i posted ?



.
currentdb.execute "insert into  tblZipCodeHistory(ZipEntered, TimeStamp) values ('" & me.txtZipEntered & "', #" & Now() & "#)"
0
 
cansevinAuthor Commented:
Yes... I used the code below. The whole thing was highlighted.



Private Sub cmdFindInstructor_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    CurrentDb.Execute "insert into  tblZipCodeHistory(ZipEntered, TimeStamp) values ('" & Me.txtZipEntered & "', Now())"
End Sub

Open in new window

0
 
ste5anSenior DeveloperCommented:
@cansevin:
Have you placed it inside of your button click event code? I've tested it, the syntax is correct. What Access version do you use?

@Rey
Don't use string concatenation with Now() or Date(). It may work or not, this depends on the localisation of your OS/Access. Using the fix US format on the opposite works always.
0
 
ste5anSenior DeveloperCommented:
@cansevin:

MouseDown is normally the wrong event. Use the On Click event.
0
 
cansevinAuthor Commented:
Thanks ste5an: I am using Access 2007, the following code:

Private Sub cmdFindInstructor_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim Statement As String

Statement = "INSERT INTO tblZipCodeHistory (ZipEntered, TimeStamp ) VALUES ( '{0}', {1} ); "
Statement = Replace(Statement, "{0}", txtZipEntered.Value)
Statement = Replace(Statement, "{1}", Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#"))
CurrentDb.Execute Statement, dbFailOnError Or dbSeeChanges
End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
@cansevin

use this


currentdb.execute "insert into  tblZipCodeHistory(ZipEntered, TimeStamp) values ('" & me.txtZipEntered & "', #" & Now() & "#)"


and use the click event of the button not mousedown
0
 
PatHartmanCommented:
This avoids converting Now() to a string.  It will be processed by the database engine as a function instead of by VBA as a string.

currentdb.execute "insert into  tblZipCodeHistory(ZipEntered, TimeStamp) values ('" & me.txtZipEntered & "', Now() )"
0
 
cansevinAuthor Commented:
My "on Click" already has a macro... so it is opening some confusing macro page. Is there a way around this? If you can't tell... I don't know much of what I am doing.
0
 
ste5anSenior DeveloperCommented:
Note the macro name. Create a VBA event procedure instead of it. Insert to code from above. Add DoCmd.RunMacro "YourNotedMacroName" at the end of the event procedure.
0
 
Rey Obrero (Capricorn1)Commented:
do you know how to edit the macro?
0
 
cansevinAuthor Commented:
The Macro name is simply "Open Report". I believe this is a generic macro that i don't want to change.

Should I create a new macro?
0
 
ste5anSenior DeveloperCommented:
Just chain it.
0
 
cansevinAuthor Commented:
Ok... any chance you can walk me through the process? I looking at a macro tools sheet with columns of Macro Name, Condition, Action, Arguments, Comment.

Where can I go to copy and past the code? Thanks!
0
 
cansevinAuthor Commented:
Ste5an,

I am now using the proper "On Click". It is still saying an error and then highlighting that last row. I am using this code:

Private Sub cmdFindInstructor_Click()
DoCmd.OpenReport "rptZipCodeLookup", acViewPreview

Dim Statement As String

Statement = "INSERT INTO tblZipCodeHistory (ZipEntered, TimeStamp ) VALUES ( '{0}', {1} ); "
Statement = Replace(Statement, "{0}", txtZipEntered.Value)
Statement = Replace(Statement, "{1}", Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#"))
CurrentDb.Execute Statement, dbFailOnError Or dbSeeChanges

End Sub

Open in new window

0
 
cansevinAuthor Commented:
Rey... yours still highlights the entire code.

Any idea what I could be doing wrong?
0
 
ste5anSenior DeveloperCommented:
What error?

Private Sub cmdFindInstructor_Click()

  On Local Error GoTo LocalError

  Dim Statement As String

  Statement = "INSERT INTO tblZipCodeHistory (ZipEntered, TimeStamp ) VALUES ( '{0}', {1} ); "
  Statement = Replace(Statement, "{0}", txtZipEntered.Value)
  Statement = Replace(Statement, "{1}", Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#"))
  CurrentDb.Execute Statement, dbFailOnError Or dbSeeChanges

  DoCmd.OpenReport "rptZipCodeLookup", acViewPreview

  Exit Sub

LocalError:
  MsgBox Err.Number & vbCrLf & vbCrLf & _
  Err.Description & & vbCrLf & vbCrLf & _
  Statement

End Sub

Open in new window


Try it also without the dbSeeChanges.
0
 
cansevinAuthor Commented:
Ste5an... I appreciate you helping on this. I tried without dbSeeChanges. Still same thing.

I copy and pasted the new code. First it highlights the "LocalError" part in red (right after pasting). Then when I try and hit the button. It pulls up an error. The error has the first line highlighted in yellow and the LocalError part is simply highlighted.
0
 
ste5anSenior DeveloperCommented:
Again, what Access version do you use? What kind of project is it (database type)?
0
 
cansevinAuthor Commented:
Access 2007. Not sure the database type... it is a split database. Is that relevant?
0
 
ste5anSenior DeveloperCommented:
It's a normal form we're talking about?
0
 
cansevinAuthor Commented:
Yes it is... normal form.
0
 
Rey Obrero (Capricorn1)Commented:
@cansevin

what is the error you get ?
0
 
ste5anSenior DeveloperCommented:
@cansevin: Please make a screen shot from where you have placed the code.
0
 
cansevinAuthor Commented:
Thanks Stean for the help. Attached is a screen shot of my code as well as the error.
screen-shot.pdf
0
 
ste5anSenior DeveloperCommented:
Place
MsgBox Statement

Open in new window

before the CurrentDb line.

Also place an
Option Explicit

Open in new window

into the second line right after Option Compare Database.
0
 
cansevinAuthor Commented:
Thanks! So the "option explicit" goes before the Private Sub?
0
 
ste5anSenior DeveloperCommented:
Yes. It's a directive to hunt down typos in the code.
0
 
cansevinAuthor Commented:
Good news, something different happened. Bad news... an error still pops up. Attached I have my code and the error.
screen-shot-2.pdf
0
 
ste5anSenior DeveloperCommented:
This looks good. Have we correctly spelled the table name and the column names?

Test it again with this code using the error handler:

Private Sub cmdFindInstructor_Click()

  On Local Error GoTo LocalError

  Dim Statement As String

  Statement = "INSERT INTO tblZipCodeHistory (ZipEntered, TimeStamp ) VALUES ( '{0}', {1} ); "
  Statement = Replace(Statement, "{0}", txtZipEntered.Value)
  Statement = Replace(Statement, "{1}", Format(Now, "\#m\/d\/yyyy hh\:nn\:ss\#"))
  CurrentDb.Execute Statement, dbFailOnError Or dbSeeChanges

  DoCmd.OpenReport "rptZipCodeLookup", acViewPreview

  Exit Sub

LocalError:
  MsgBox Err.Number & vbCrLf & vbCrLf & _
  Err.Description & & vbCrLf & vbCrLf & _
  Statement

End Sub
                                            

Open in new window

0
 
cansevinAuthor Commented:
The message box popped up. I do have an "On Current" for the form. Could that be affecting it?
screen-shot-3.pdf
0
 
ste5anSenior DeveloperCommented:
Seems that you have some screwed up the event procedures.

Use the menu Debug/Compile to find more such errors.

Also it's time to ask: Do you have backups of your work? If not, do so.

Remove the enitre Private Sub cmdFindInstructor_Click(). And try to run your form.

So right click on your button and select the create event menu item. Select code macro, then paste the code into the sub procedure. Don't copy the rivate Sub cmdFindInstructor_Click() [..] End Sub from the samples above.
0
 
cansevinAuthor Commented:
Thanks Ste@an... I am out of time. I appreciate all your help. I will post another question to try to fix it. I will close this question giving you the point.

What would you suggest I word the question as? What exactly is my form doing wrong that needs fixed?

I appreciate all your help!
0
 
ste5anSenior DeveloperCommented:
Before doing so:

Create a copy of your database. Remove everything from it. Only keep your table tblZipCodeHistory and your form.

Remove the data source from the form, if there is one.
Remove all controls from the form, keep only the two text boxes and the button.
Remove all code from the form's module.

Then retry to apply the code from above as VBA event procedure.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 16
  • 14
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now