?
Solved

Save number entered

Posted on 2014-03-27
36
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 14
  • 5
  • +1
36 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 35

Accepted Solution

by:
ste5an earned 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 35

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 35

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

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 35

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 35

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
 
LVL 35

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 35

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 35

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39959866
@cansevin

what is the error you get ?
0
 
LVL 35

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 35

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 35

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 35

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 35

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 35

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

719 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