Adding bunch of data in table usnig VBA

[Hasanain F. Esmail]

Hi,
Your help will be greatly appriciated.

I have a table with following fields. Table's name is tblTicketNumers

TicketNumberID (It is a key field)
TicketNumber (Text field)
AgentsName (Text field)

I have a form which is NOT BOUND to this table with following controls

txtBeginingNumber (Type in the first number of ticket)
txtEndNumber (Type in the last number of ticket)
cboAgentsName (This is a lookup control, gets data from another table)

Typical usage:
Enter 510 in txtBeginingNumber textbox
Enter 580 in txtEndNumber textbox
select any agent's name from cboAgentsName

Press a command button.

Behind a command button I am looking for a code to enter this data into
tblTicketNumbers. After executing this code, the table should look like
this:

TicketNumberID TicketNumber AgentsName
1 510 Skylink Travel and Tours
2 511 Skylink Travel and Tours
3 512 Skylink Travel and Tours
4 513 Skylink Travel and Tours
5 514 Skylink Travel and Tours

The table should be filled with data all the way to ticket number 580

Thanking you all in advance.

[Tom van Stiphout]

On 22 Jan 2004 03:05:44 -0800, ha*******@yahoo.com (Hasanain F.
Esmail) wrote:

Sorry Hasanain, but the newsgroup is not a code factory. The way it
works is: you struggle, read the help files, find online code
snippets, and when you get stuck you post your efforts so far, and
hopefully others can provide help.
Good luck with working on your new skill.

-Tom.

Thanks Tom.

I agree with your design, I should be using AgentID.

I will appriciate if you could help me with VBA code since I am not
good at it.

Thanks again.

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<bs********************************@4ax.com>. ..

On 21 Jan 2004 21:08:37 -0800, ha*******@yahoo.com (Hasanain F.
Esmail) wrote:

Bad design.
If you want a relational database, rather than AgentsName you would
use AgentID. This ID value can be looked up in the Agents table to
find the Name, Phone, etc for the agent.

Your cboAgentsName now will have (at least) 2 columns: a hidden
AgentID, and the visible AgentName.

To add the rows to the table, use a recordset.
Then in a For loop, loop from txtBeginNumber to txtEndNumber, use
AddNew to add a new row, and set the values for each field. .Update
saves the row.

-Tom.

>Hi,
>Your help will be greatly appriciated.
>
>I have a table with following fields. Table's name is tblTicketNumers
>
>TicketNumberID (It is a key field)
>TicketNumber (Text field)
>AgentsName (Text field)
>
>I have a form which is NOT BOUND to this table with following controls
>
>txtBeginingNumber (Type in the first number of ticket)
>txtEndNumber (Type in the last number of ticket)
>cboAgentsName (This is a lookup control, gets data from another table)
>
>Typical usage:
>Enter 510 in txtBeginingNumber textbox
>Enter 580 in txtEndNumber textbox
>select any agent's name from cboAgentsName
>
>Press a command button.
>
>Behind a command button I am looking for a code to enter this data into
>tblTicketNumbers. After executing this code, the table should look like
>this:
>
>TicketNumberID TicketNumber AgentsName
>1 510 Skylink Travel and Tours
>2 511 Skylink Travel and Tours
>3 512 Skylink Travel and Tours
>4 513 Skylink Travel and Tours
>5 514 Skylink Travel and Tours
>
>The table should be filled with data all the way to ticket number 580
>
>Thanking you all in advance.

[Hasanain F. Esmail]

Hi All,
Thanks to all who helped me.
Thanks to Tom van Stiphout, he forced me to go back to my library.

I solved the problem

My table called tblTicket has three fielsd
TicketID (Autonumber)
TicketNumber
AgentID

The other table I have is called tblAgent with two fields
AgentID (AutoNumber)
AgentName

In my form called frmTicketNumber I have two textboxes and one combobox
txtStart
txtStop
cboAgentID

I have a command button and behind this butto the code is as follows

Private Sub cmdCallCreateRecords_Click()

Dim MyStart As Long
Dim MyStop As Long
Dim MyAgent As Long

Me.txtStart.SetFocus
MyStart = CLng(Me.txtStart.Text)
Me.txtStop.SetFocus
MyStop = CLng(Me.txtStop.Text)
Me.cboAgentID.SetFocus
MyAgent = CLng(Me.cboAgentID.Value)

CreateRecords MyStart, MyStop, MyAgent

End Sub

Sub CreateRecords(lngStart As Long, lngStop As Long, lngAgent As Long)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCounter As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTicket", dbOpenTable)

For lngCounter = lngStart To lngStop
With rst
.AddNew
.Fields("TicketNo") = lngCounter
.Fields("AgentID") = lngAgent
.Update
End With
Next lngCounter

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

This works fine for the intended purpose
Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<r7********************************@4ax.com>. ..

On 22 Jan 2004 03:05:44 -0800, ha*******@yahoo.com (Hasanain F.
Esmail) wrote:

Sorry Hasanain, but the newsgroup is not a code factory. The way it
works is: you struggle, read the help files, find online code
snippets, and when you get stuck you post your efforts so far, and
hopefully others can provide help.
Good luck with working on your new skill.

-Tom.

Thanks Tom.

I agree with your design, I should be using AgentID.

I will appriciate if you could help me with VBA code since I am not
good at it.

Thanks again.

Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<bs********************************@4ax.com>. ..

On 21 Jan 2004 21:08:37 -0800, ha*******@yahoo.com (Hasanain F.
Esmail) wrote:

Bad design.
If you want a relational database, rather than AgentsName you would
use AgentID. This ID value can be looked up in the Agents table to
find the Name, Phone, etc for the agent.

Your cboAgentsName now will have (at least) 2 columns: a hidden
AgentID, and the visible AgentName.

To add the rows to the table, use a recordset.
Then in a For loop, loop from txtBeginNumber to txtEndNumber, use
AddNew to add a new row, and set the values for each field. .Update
saves the row.

-Tom.
>Hi,
>Your help will be greatly appriciated.
>
>I have a table with following fields. Table's name is tblTicketNumers
>
>TicketNumberID (It is a key field)
>TicketNumber (Text field)
>AgentsName (Text field)
>
>I have a form which is NOT BOUND to this table with following controls
>
>txtBeginingNumber (Type in the first number of ticket)
>txtEndNumber (Type in the last number of ticket)
>cboAgentsName (This is a lookup control, gets data from another table)
>
>Typical usage:
>Enter 510 in txtBeginingNumber textbox
>Enter 580 in txtEndNumber textbox
>select any agent's name from cboAgentsName
>
>Press a command button.
>
>Behind a command button I am looking for a code to enter this data into
>tblTicketNumbers. After executing this code, the table should look like
>this:
>
>TicketNumberID TicketNumber AgentsName
>1 510 Skylink Travel and Tours
>2 511 Skylink Travel and Tours
>3 512 Skylink Travel and Tours
>4 513 Skylink Travel and Tours
>5 514 Skylink Travel and Tours
>
>The table should be filled with data all the way to ticket number 580
>
>Thanking you all in advance.

[Tom van Stiphout]

On 25 Jan 2004 02:11:16 -0800, ha*******@yahoo.com (Hasanain F.
Esmail) wrote:

That's the spirit ! Good for you !
-Tom.

Hi All,
Thanks to all who helped me.
Thanks to Tom van Stiphout, he forced me to go back to my library.

I solved the problem

My table called tblTicket has three fielsd
TicketID (Autonumber)
TicketNumber
AgentID

The other table I have is called tblAgent with two fields
AgentID (AutoNumber)
AgentName

In my form called frmTicketNumber I have two textboxes and one combobox
txtStart
txtStop
cboAgentID

I have a command button and behind this butto the code is as follows

Private Sub cmdCallCreateRecords_Click()

Dim MyStart As Long
Dim MyStop As Long
Dim MyAgent As Long

Me.txtStart.SetFocus
MyStart = CLng(Me.txtStart.Text)
Me.txtStop.SetFocus
MyStop = CLng(Me.txtStop.Text)
Me.cboAgentID.SetFocus
MyAgent = CLng(Me.cboAgentID.Value)

CreateRecords MyStart, MyStop, MyAgent

End Sub

Sub CreateRecords(lngStart As Long, lngStop As Long, lngAgent As Long)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCounter As Long

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblTicket", dbOpenTable)

For lngCounter = lngStart To lngStop
With rst
.AddNew
.Fields("TicketNo") = lngCounter
.Fields("AgentID") = lngAgent
.Update
End With
Next lngCounter

rst.Close
Set rst = Nothing
Set dbs = Nothing

End Sub

This works fine for the intended purpose
Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<r7********************************@4ax.com>. ..

On 22 Jan 2004 03:05:44 -0800, ha*******@yahoo.com (Hasanain F.
Esmail) wrote:

Sorry Hasanain, but the newsgroup is not a code factory. The way it
works is: you struggle, read the help files, find online code
snippets, and when you get stuck you post your efforts so far, and
hopefully others can provide help.
Good luck with working on your new skill.

-Tom.

>Thanks Tom.
>
>I agree with your design, I should be using AgentID.
>
>I will appriciate if you could help me with VBA code since I am not
>good at it.
>
>Thanks again.
>
>Tom van Stiphout <to*****@no.spam.cox.net> wrote in message news:<bs********************************@4ax.com>. ..
>> On 21 Jan 2004 21:08:37 -0800, ha*******@yahoo.com (Hasanain F.
>> Esmail) wrote:
>>
>> Bad design.
>> If you want a relational database, rather than AgentsName you would
>> use AgentID. This ID value can be looked up in the Agents table to
>> find the Name, Phone, etc for the agent.
>>
>> Your cboAgentsName now will have (at least) 2 columns: a hidden
>> AgentID, and the visible AgentName.
>>
>> To add the rows to the table, use a recordset.
>> Then in a For loop, loop from txtBeginNumber to txtEndNumber, use
>> AddNew to add a new row, and set the values for each field. .Update
>> saves the row.
>>
>> -Tom.
>>
>>
>> >Hi,
>> >Your help will be greatly appriciated.
>> >
>> >I have a table with following fields. Table's name is tblTicketNumers
>> >
>> >TicketNumberID (It is a key field)
>> >TicketNumber (Text field)
>> >AgentsName (Text field)
>> >
>> >I have a form which is NOT BOUND to this table with following controls
>> >
>> >txtBeginingNumber (Type in the first number of ticket)
>> >txtEndNumber (Type in the last number of ticket)
>> >cboAgentsName (This is a lookup control, gets data from another table)
>> >
>> >Typical usage:
>> >Enter 510 in txtBeginingNumber textbox
>> >Enter 580 in txtEndNumber textbox
>> >select any agent's name from cboAgentsName
>> >
>> >Press a command button.
>> >
>> >Behind a command button I am looking for a code to enter this data into
>> >tblTicketNumbers. After executing this code, the table should look like
>> >this:
>> >
>> >TicketNumberID TicketNumber AgentsName
>> >1 510 Skylink Travel and Tours
>> >2 511 Skylink Travel and Tours
>> >3 512 Skylink Travel and Tours
>> >4 513 Skylink Travel and Tours
>> >5 514 Skylink Travel and Tours
>> >
>> >The table should be filled with data all the way to ticket number 580
>> >
>> >Thanking you all in advance.