Copy record(s) from a form into table?

[ScottGem]

if i change the line as you suggested to: SQL_Text ="Insert into main (SHIFTNAME) VALUES('" & Me.shiftname & "');"

i get the following message: "cant append all recorsds in the append query. 1 record due to validation rule violations, do you wish to run the query anyway yes/no, if i click yes it doesnt do anything (main table is emtty) if i click no it says" runtime error: 2501, the runsql action was cancelled"

The fact that you got that message shows that the SQL statement weas syntactically correct, because Access did execute it. However, as the message says (and WorkMad explained) there is a conflict with the data you are trying to append and rules for that field. You need to research that.

 

[KevinSlater]

Ok many thanks scottgem & workmad for your help, couldnt have done this without your support!. I re-created the form this code is on, & checked the fields in my table (i didnt have any validation rules set on the fields i was copying to...bit strange),the below code works fine:

however I have one last hurdle im having trouble with...on this form is a sub form "subformeopleonshift" with some fields (firstname,lastname, initials) that i would also like to copy over into the "MAIN" table when the user enters them in the form but cant get it to work, the code will partly work if done directly in the sub-form instead on its own but it creates 2 (duplicate) records in the "main" table which is no good, any suggestions?
----------------------------------------------------------------------
Private Sub Form_AfterUpdate()
Dim SQL_Text As String

SQL_Text = "INSERT INTO MAIN (SHIFTNAME, SHIFT_DATE) SELECT '" & Me.SHIFTNAME & "','" & Me.SHIFT_DATE & "'"

DoCmd.RunSQL (SQL_Text)
End Sub
------------------------------------------------------------------------

 

[ScottGem]

Kevin,

You're doing it again. You are mixing Select for value lists.

Also you shouldn't be copying data into multiple tables. The only thing you need in a related table is a foreign key to link the the two.

 

[KevinSlater]

Ok ill bear that in mind in the future, i know what im doing may seem strange, but the code appears to work anyway, but i believe i need to copy the data into a table rather than relating records in tables for what im trying to do

 

[ScottGem]

Ok ill bear that in mind in the future, i know what im doing may seem strange, but the code appears to work anyway, but i believe i need to copy the data into a table rather than relating records in tables for what im trying to do

I can think of extremely few situations where you have to have redundant data in multiple tables instead of using queries. Properly normalizing your database is the key to efficiency. A large percentage of the questions we deal with on this and other Access sites are the result of improper design. If the design had been done correctly the problems wouldn't have occured.

If you want to explain why you think you need to do what you are doing we can advise further.

 

[cafedude]

Hello All

I am not new to the computer world but am new to the creation of data bases world.

I sell products from a catalog. We have customers and prospects. A prospect is just that; a future customer. What I am needing to do is to create a button that will take my prospect data and save it to my Customer table. Hence once a prospect buys a product they are then moved to the "customer" table.

How would I accomplish this? Any and all help would be most appreciated!

 

[ScottGem]

Hello All

I am not new to the computer world but am new to the creation of data bases world.

I sell products from a catalog. We have customers and prospects. A prospect is just that; a future customer. What I am needing to do is to create a button that will take my prospect data and save it to my Customer table. Hence once a prospect buys a product they are then moved to the "customer" table.

How would I accomplish this? Any and all help would be most appreciated!

First its not a good idea to piggyback your question on someone elses. This can lead to confusion. You should start a new thread.

You really shouldn't have two tables. You should have a field that indicates whether a person is a prospect or a customer. Then all you would need to do is change the value of that field.

 

[sanalsurabhi]

How to make calculations in an unbound text box on access form