Inserting values into a table from a textbox

[Annoscia85]

Hi,

I've been working on this for a few hours..

I have a form with a few textboxes on it, they all have source control, these work fine and all the data entered by the user is inserted into the correct tables.

Also on this form i have a textbox called Revision, I need this unbound as I need this to be inputted into two tables, Costing_Main table and Costing_Sub table, this is my code....

CurrentDb.Execute "Insert into Costing_Main (Revision) Values ('" & Me.Revision & "')"
CurrentDb.Execute "Insert into Costing_Sub (Revision) Values ('" & Me.Revision & "')"

I receive no error message, no run-time error's...nothing

can anybody help??

 

[pbaldy]

Is the code running? What event is it in? Add this to see if there's an error from the SQL:

CurrentDb.Execute "Insert into Costing_Main (Revision) Values ('" & Me.Revision & "')", dbFailOnError

 

[Annoscia85]

Hey Paul,

I've been thinking that because I'm trying to insert the revision at the same time as inserting the data from the textboxes with the control source attached to them that maybe I should be using an update query?


This is my code so far to add the Revision textbox values to the tables
Private Sub Revision_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE Costing_Main SET Revision = '" & Revision & "' WHERE Part_No = '" & Part_No & "'"
strSQL = "UPDATE Costing_Sub SET Revision = '" & Revision & "' WHERE Enquiry_No = '" & Enquiry_No & "'"


End Sub

many thanks for this Paul

 

[pbaldy]

If those are the tables that were affected by the bound textboxes, then you probably do want to update. The code you posted won't actually do anything, as you never execute the SQL.

 

[Annoscia85]

Thanks Paul,

Is this where..."CurrentDb.Execute (strSQL)" comes in?

Private Sub Revision_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE Costing_Main SET Revision = '" & Me.Revision & "' WHERE Part_No = '" & Me.Part_No & "'"
strSQL = "UPDATE Costing_Sub SET Revision = '" & Me.Revision & "' WHERE Enquiry_No = '" & Me.Enquiry_No & "'"

CurrentDb.Execute (strSQL)

End Sub

 

[pbaldy]

Yes, but with that code it would only execute the second one. When you set the string the second time you overwrite what was there. You'd need to set the string, execute, set the string again, execute again.

 

[Annoscia85]

Hi Paul,

I have tried what you suggested and still the revision is not updated in the tables..

this is my code..


Private Sub Revision_AfterUpdate()

Dim strSQL As String

strSQL = "UPDATE Costing_Main SET Revision = '" & Me.Revision & "' WHERE Part_No = '" & Me.Part_No & "'"
CurrentDb.Execute (strSQL)


strSQL = "UPDATE Costing_Sub SET Revision = '" & Me.Revision & "' WHERE Enquiry_No = '" & Me.Enquiry_No & "'"
CurrentDb.Execute (strSQL)

End Sub


is there anything that I'm doing wrong or that you could think of?

 

[Annoscia85]

Hi Paul,
It stil wouldn't work after trying everything, so I've built it as below. It's Long winded but it gets the job done!!

Private Sub Revision_AfterUpdate()

Dim p As String
'costing_main
p = Me.[Part_No].Value
p = Me.[Enquiry_No].Value
p = Me.[Description].Value
p = Me.[Neida_Part_No].Value
p = Me.[Current_Date].Value
p = Me.[Revision].Value
'costing_sub
p = Me.[Qty_Price_Break].Value
p = Me.[Enquiry_No].Value
p = Me.[Machine_Type].Value
p = Me.[Rate_Hr].Value
p = Me.[Cycle_ppm].Value
p = Me.[Setting_Time].Value
p = Me.[Tool_Cost1].Value
p = Me.[Tool_Cost2].Value
p = Me.[Revision].Value

CurrentDb.Execute "INSERT INTO Costing_Main (Enquiry_No, Part_No, Description, Neida_Part_No, Current_Date, Revision)" _
& "VALUES ('" & Enquiry_No & "', '" & Part_No & "', '" & Description & "', '" & Neida_Part_No & "', '" & Current_Date & "', '" & Revision & "');"

CurrentDb.Execute "INSERT INTO Costing_Sub ( Qty_Price_Break, Enquiry_No, Machine_Type, Rate_Hr, Cycle_ppm, Setting_Time, Tool_Cost1, Tool_Cost2, Revision )" _
& "VALUES ('" & Qty_Price_Break & "', '" & Enquiry_No & "', '" & Machine_Type & "', '" & Rate_Hr & "', '" & Cycle_ppm & "', " _
& "'" & Setting_Time & "', '" & Tool_Cost1 & "', '" & Tool_Cost2 & "', '" & Revision & "');"

Many thanks for your help and replies Paul!

 

[pbaldy]

Glad you got it working. You never did add the dbFailOnError that would have let us know if it was a problem with the SQL. What does all that first part do? You don't use it in the SQL, and P would end up with the value in Revision since it will keep overwriting itself.

 

[Annoscia85]

Hi Paul,

I have taken all the first part out now that I realise it did nothing!!

I did at the dbFailOnError and it came up with the error "dbFailOnError = 128".

Thanks for all the help Paul!