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.

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
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

"domibud" <(E-Mail Removed)> wrote in message
news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
> 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.

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.).

"Jeff Boyce" wrote:

> 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
> http://mvp.support.microsoft.com/
>
> Microsoft IT Academy Program Mentor
> http://microsoftitacademy.com/
>
> "domibud" <(E-Mail Removed)> wrote in message
> news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
> > 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.
>
>

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


"domibud" wrote:

> 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.).
>
> "Jeff Boyce" wrote:
>
> > 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
> > http://mvp.support.microsoft.com/
> >
> > Microsoft IT Academy Program Mentor
> > http://microsoftitacademy.com/
> >
> > "domibud" <(E-Mail Removed)> wrote in message
> > news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
> > > 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.
> >
> >

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
>
>
> "domibud" wrote:
>
>> 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.).
>>
>> "Jeff Boyce" wrote:
>>
>>> 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
>>> http://mvp.support.microsoft.com/
>>>
>>> Microsoft IT Academy Program Mentor
>>> http://microsoftitacademy.com/
>>>
>>> "domibud" <(E-Mail Removed)> wrote in message
>>> news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
>>>> 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.
>>>

Thanks John for your advice. I'll post again about the result; I just read
your reply.

Those 3 fields, [#1], [#2-#8], and [#9] represent the taxi voucher series
number (9 digits).
1. [#1] --> a number between 1 and 9, sequencial, and it will reset to 1
again.
2. [#2-#8] --> 7 digits number and sequencial
3. [#9] --> static number

John, I'd like to ask something about your code. There's some part that I
don't understand.

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

I don't understand the FROM clause. What is tNumbers represent? You said
it's for number table. What is number table means here?

Thanks for your time.

Regards,

Budi


"John Spencer" wrote:

> 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
> >
> >
> > "domibud" wrote:
> >
> >> 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.).
> >>
> >> "Jeff Boyce" wrote:
> >>
> >>> 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
> >>> http://mvp.support.microsoft.com/
> >>>
> >>> Microsoft IT Academy Program Mentor
> >>> http://microsoftitacademy.com/
> >>>
> >>> "domibud" <(E-Mail Removed)> wrote in message
> >>> news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
> >>>> 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.
> >>>
>

A numbers table is just that a table of numbers from 1 to N where N is the
largest number you need. 1 to 9999999 in your case. However, there is a way
to handle that with just ten records in the table. One field named Counter
with the values 0 to 9 in a table named tNumbers.

Your original loop concept may be faster if you are creating a limited number
of vouchers at any one time.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

domibud wrote:
> Thanks John for your advice. I'll post again about the result; I just read
> your reply.
>
> Those 3 fields, [#1], [#2-#8], and [#9] represent the taxi voucher series
> number (9 digits).
> 1. [#1] --> a number between 1 and 9, sequencial, and it will reset to 1
> again.
> 2. [#2-#8] --> 7 digits number and sequencial
> 3. [#9] --> static number
>
> John, I'd like to ask something about your code. There's some part that I
> don't understand.
>
> 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
>
> I don't understand the FROM clause. What is tNumbers represent? You said
> it's for number table. What is number table means here?
>
> Thanks for your time.
>
> Regards,
>
> Budi
>
>
> "John Spencer" wrote:
>
>> 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
>>>
>>>
>>> "domibud" wrote:
>>>
>>>> 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.).
>>>>
>>>> "Jeff Boyce" wrote:
>>>>
>>>>> 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
>>>>> http://mvp.support.microsoft.com/
>>>>>
>>>>> Microsoft IT Academy Program Mentor
>>>>> http://microsoftitacademy.com/
>>>>>
>>>>> "domibud" <(E-Mail Removed)> wrote in message
>>>>> news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
>>>>>> 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.

I have it running now.
And thanks for your explanation.

Regards,

Budi

"John Spencer" wrote:

> A numbers table is just that a table of numbers from 1 to N where N is the
> largest number you need. 1 to 9999999 in your case. However, there is a way
> to handle that with just ten records in the table. One field named Counter
> with the values 0 to 9 in a table named tNumbers.
>
> Your original loop concept may be faster if you are creating a limited number
> of vouchers at any one time.
>
> John Spencer
> Access MVP 2002-2005, 2007-2008
> Center for Health Program Development and Management
> University of Maryland Baltimore County
>
> domibud wrote:
> > Thanks John for your advice. I'll post again about the result; I just read
> > your reply.
> >
> > Those 3 fields, [#1], [#2-#8], and [#9] represent the taxi voucher series
> > number (9 digits).
> > 1. [#1] --> a number between 1 and 9, sequencial, and it will reset to 1
> > again.
> > 2. [#2-#8] --> 7 digits number and sequencial
> > 3. [#9] --> static number
> >
> > John, I'd like to ask something about your code. There's some part that I
> > don't understand.
> >
> > 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
> >
> > I don't understand the FROM clause. What is tNumbers represent? You said
> > it's for number table. What is number table means here?
> >
> > Thanks for your time.
> >
> > Regards,
> >
> > Budi
> >
> >
> > "John Spencer" wrote:
> >
> >> 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
> >>>
> >>>
> >>> "domibud" wrote:
> >>>
> >>>> 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.).
> >>>>
> >>>> "Jeff Boyce" wrote:
> >>>>
> >>>>> 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
> >>>>> http://mvp.support.microsoft.com/
> >>>>>
> >>>>> Microsoft IT Academy Program Mentor
> >>>>> http://microsoftitacademy.com/
> >>>>>
> >>>>> "domibud" <(E-Mail Removed)> wrote in message
> >>>>> news:418C07E5-9E9D-43E0-9520-(E-Mail Removed)...
> >>>>>> 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.
>