Etnering Value at one shot 1

[bells]

I have table1 and table2

In table1 I have

stID (PK)
Name
dateOfBirth
schoolId

in table2 I have

serviceId(PK)
serviceType
stId

the two tables are related using the stID key

stID is numerical field starting 100 and I have about 200 records in the first table (The number might increase in the future)
Now, Single data entry for one person is working fine
Now what I want is that I want to enter the serviceType value for all people in table 1 with one shot
Is it possible ???
Thanks in Advance

 

[dhookom]

You should be able to do this with an append query:

INSERT INTO table2 (StID, ServiceType)
SELECT StID, [serviceType value for all people] FROM Table1;

Duane
Hook'D on Access
MS Access MVP

 

[bells]

Well thank you dHookom how can I do this for all stID in table1
In other words how to enter values in table2 for all stID in table1
I am sure I have to do some kind of loop but do not remember how?
I am intending to put it in a command button.

thanks

 

[dhookom]

Did you try what I suggested before you replied? If so, what were the results?

Duane
Hook'D on Access
MS Access MVP

 

[bells]

Hello dHookom

Yes I tried it using

Private Sub Command15_Click()

Dim strSQL As String

strSQL = "INSERT INTO table2(stId, serviceType) " & _
"Select stId from table1" & "' Me.txtServiceType'"

DoCmd.RunSQL strSQL

End sub
But when I click the button nothing happens and also nothing has entered in table2
any Idea??
Thanks for your time

 

[bells]

Now I started clean and received a message

"Number of query values and destination fields are not the same" messages. any Idea ??/

 

[dhookom]

Try:
Private Sub Command15_Click()
Dim strSQL As String
strSQL = "INSERT INTO table2(stId, serviceType) " & _
"Select stId [red], '" & Me.txtServiceType & "' from table1"[/red]
DoCmd.RunSQL strSQL
End sub

Duane
Hook'D on Access
MS Access MVP

 

[bells]

Now it works fine
Thank u very much dhookom

 

[bells]

Sorry for coming back again dhookom

You know this thing works perfect in Access 2003 and enters all 100 records, but after a while when I test it using Access 2007 it is inserting only one record and even with that record the [stid] value is not inserted. any idea about this. Thanks in Advance

 

[dhookom]

Debug the code to see what is happening.

Duane
Hook'D on Access
MS Access MVP

 

[bells]

The Security feature of 2007 which disables VBA, and Macros was ON. Now I disable that and Now it working perfectly well in both 2003, and 2007.
Thanks