Solved

Save number entered

Posted on 2014-03-27
36
264 Views
Last Modified: 2014-03-27
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
Comment
Question by:cansevin
  • 16
  • 14
  • 5
  • +1
36 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39959228
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
 
LVL 32

Accepted Solution

by:
ste5an earned 500 total points
ID: 39959236
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
 

Author Comment

by:cansevin
ID: 39959287
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39959293
@cansevin


did   you try the code i posted ?



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

Author Comment

by:cansevin
ID: 39959311
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
 
LVL 32

Expert Comment

by:ste5an
ID: 39959314
@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
 
LVL 32

Expert Comment

by:ste5an
ID: 39959320
@cansevin:

MouseDown is normally the wrong event. Use the On Click event.
0
 

Author Comment

by:cansevin
ID: 39959322
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39959323
@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
 
LVL 34

Expert Comment

by:PatHartman
ID: 39959334
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
 

Author Comment

by:cansevin
ID: 39959336
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
 
LVL 32

Expert Comment

by:ste5an
ID: 39959347
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39959349
do you know how to edit the macro?
0
 

Author Comment

by:cansevin
ID: 39959363
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
 
LVL 32

Expert Comment

by:ste5an
ID: 39959374
Just chain it.
0
 

Author Comment

by:cansevin
ID: 39959383
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
 

Author Comment

by:cansevin
ID: 39959690
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
 

Author Comment

by:cansevin
ID: 39959706
Rey... yours still highlights the entire code.

Any idea what I could be doing wrong?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 32

Expert Comment

by:ste5an
ID: 39959707
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
 

Author Comment

by:cansevin
ID: 39959732
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
 
LVL 32

Expert Comment

by:ste5an
ID: 39959794
Again, what Access version do you use? What kind of project is it (database type)?
0
 

Author Comment

by:cansevin
ID: 39959811
Access 2007. Not sure the database type... it is a split database. Is that relevant?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39959838
It's a normal form we're talking about?
0
 

Author Comment

by:cansevin
ID: 39959852
Yes it is... normal form.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39959866
@cansevin

what is the error you get ?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39959888
@cansevin: Please make a screen shot from where you have placed the code.
0
 

Author Comment

by:cansevin
ID: 39959904
Thanks Stean for the help. Attached is a screen shot of my code as well as the error.
screen-shot.pdf
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39959988
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
 

Author Comment

by:cansevin
ID: 39959997
Thanks! So the "option explicit" goes before the Private Sub?
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39960009
Yes. It's a directive to hunt down typos in the code.
0
 

Author Comment

by:cansevin
ID: 39960021
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
 
LVL 32

Expert Comment

by:ste5an
ID: 39960032
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
 

Author Comment

by:cansevin
ID: 39960058
The message box popped up. I do have an "On Current" for the form. Could that be affecting it?
screen-shot-3.pdf
0
 
LVL 32

Expert Comment

by:ste5an
ID: 39960075
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
 

Author Comment

by:cansevin
ID: 39960087
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
 
LVL 32

Expert Comment

by:ste5an
ID: 39960096
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now