Don't create the query, just execute the query string. See the modification
below.
Do
...
'===================================================================
strSQL = "INSERT INTO BlueBirdVoucherReceipt " & _
"([#1],[#2-#8],[#9],DateReceive,ExpireDate)" & _
" VALUES (" & Digit1 & "," & Digit28 & "," & 1 & _
", #" & Me.DateReceive & "#,#" & Me.ExpireDate & "#)"
dbs.Execute StrSql,dbFailonError
'===================================================================
'Drop the following line - all it would do would be to make a query definition
'it would not execute the query.
' Set qry = dbs.CreateQueryDef("AppendReceipt", strSQL) DROP THIS
Digit28 = Digit28 + 1
Digit1 = Digit1 + 1
Loop Until Digit28 = Digit28End
Exit_SubmitReceive_Click:
Exit Sub
Err_SubmitReceive_Click:
MsgBox Err.Description
Resume Exit_SubmitReceive_Click
End Sub
There is a better way to do this using just one query and a number table
Table: tNumbers
Field: Counter (number field, type long integer) (unique index)
Then you can execute a query that looks something like the one below. I have
a problem with your field names [#1],[#2-#8],[#9] - I don't understand what
you want to put in them.
strSQL = "INSERT INTO BlueBirdVoucherReceipt " & _
"([#1],[#2-#8],[#9],DateReceive,ExpireDate)" & _
" SELECT x, y, z" & _
", #" & Me.DateReceive & "#, #" & Me.ExpireDate & "#" & _
" FROM tNumbers " & _
" WHERE Counter Between " & Me.Start " and " & Me.End
x, y, and z would have to be replaced by expressions based on the value of
counter.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
domibud wrote:
I tried using this code to append the table.
When I try running it, I got the message the Access can't find the field.
I cross checked the field names in the table with the one I put into the
code below. They're the same.
Is there another mistakes in it?
Can you point me to the right direction please?
The command button that run this code is in a form.
Private Sub SubmitReceive_Click()
On Error GoTo Err_SubmitReceive_Click
Dim Digit1 As Long
Dim Digit28 As Long
Dim Digit28End As Long
Dim dbs As DAO.Database
Dim qry As DAO.QueryDef
Dim strSQL As String
Digit1 = [Me.#1]
Digit28 = [Me.#2-#8Start]
Digit28End = [Me.#2-#8End]
Set dbs = CurrentDb
Do
If Digit1 = 10 Then
Digit1 = 1
End If
strSQL = "INSERT INTO
BlueBirdVoucherReceipt[(#1[,#2-#8[,#9[,DateReceive[,ExpireDate[,Employee]]]]])] VALUES (Digit1[,Digit28[,1[,Me.DateReceive[,Me.ExpireDate]]]])"
Set qry = dbs.CreateQueryDef("AppendReceipt", strSQL)
Digit28 = Digit28 + 1
Digit1 = Digit1 + 1
Loop Until Digit28 = Digit28End
Exit_SubmitReceive_Click:
Exit Sub
Err_SubmitReceive_Click:
MsgBox Err.Description
Resume Exit_SubmitReceive_Click
End Sub
:
Why?
This database is used to monitor taxi transport expenses. We used vouchers
from the taxi company to do so.
The monitoring will start from the time we receive the taxi voucher from the
taxi company, and we receive around 200 voucher in one time. And end when the
user input their expense detail and submit their report.
This's the start of everything.
That's why I need the query to append lots of records with just one input
from the user. The fields that I used are [Voucher #], [Date Receive], and
[Expire Date] with [Voucher #] as the primary key. And this table is the
parent table for other monitoring processes.
If I can't make the query do so, then Admin Dept. has to input all those
record one at a time, which will make this monitoring worse than that already
used here.
That's why I need the query to append all those records with just one time
input from the user (Admin Dept.).
:
Why? As in "why do you want to create a number of (nearly) blank records?"
I'm not asking out of curiosity, but because it is rarely necessary to do
so.
What will having all these allow you (and/or your users) to do that you
think they couldn't do otherwise?
--
Regards
Jeff Boyce
www.InformationFutures.net
Microsoft Office/Access MVP
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
Hi, I'm trying to make an append query to update a table in my database.
The source for the query is a form. There're 2 textbox that will define
the
range of voucher# (sequential) and a textbox to define date.
I need the query to append a table based on that information, so that the
user only need to input the append data once.
For example if the user input:
1. 000010 for textbox that define the start of the voucher# range
2. 000050 for textbpx that define the end of the voucher# range
3. 03-May-2008 for textbox that define the date
Once the user click the "Submit" command button, I need the query to
append
the tables, so that the table will become:
000010 03-May-2008
000011 03-May-2008
000012 03-May-2008
.
.
.
000048 03-May-2008
000049 03-May-2008
000050 03-May-2008
Can I do that in Access 2003?
I need this since the user will input a lot of records (around 200
records)
in one time.
Thanks for all your help.
