Bijanbijan
asked on
Adding New Records Events in Access and SQL Query
Hi Every Body
I have a question on Forms.
I am using Access in order to make my Forms and I am using SQL server for DataBase.
Here is the Scenario:
Basically I want to know what event happens when one uses the Add New Button on the Navigation Bar (the Bar on the Button of Every form which let’s you to go back and forth and Add a new data to your Table)?
I have a form, which we Call it F1 and it’s Record Source is Q1 Query, I know how to send some Data from it to another Table Table1 using a Command Button and Some VB Coding in it’s OnClick Event, But I want to be able to send the same Data when I want to add a new data to my F1 Form, without using my Button. Any suggestion?
Best Regards
Sanan
Also on the same Note the Data that I want to send is the Data that comes from a subform “SF1” on my F1 Form, Rather than Pick them one by one, I want to run a Following SQL query on this SUBForm “SF1” :
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount
FROM [Order Details]
WHERE (OrderID = )
On the Where condition I want to say that Basically the OrderID is the same as the Original F1 Order ID.
How can I do that?
And After it is done Append the Result Row or Rows to my table1 Table.
How can I do it?
Best regards
Sanan
I have a question on Forms.
I am using Access in order to make my Forms and I am using SQL server for DataBase.
Here is the Scenario:
Basically I want to know what event happens when one uses the Add New Button on the Navigation Bar (the Bar on the Button of Every form which let’s you to go back and forth and Add a new data to your Table)?
I have a form, which we Call it F1 and it’s Record Source is Q1 Query, I know how to send some Data from it to another Table Table1 using a Command Button and Some VB Coding in it’s OnClick Event, But I want to be able to send the same Data when I want to add a new data to my F1 Form, without using my Button. Any suggestion?
Best Regards
Sanan
Also on the same Note the Data that I want to send is the Data that comes from a subform “SF1” on my F1 Form, Rather than Pick them one by one, I want to run a Following SQL query on this SUBForm “SF1” :
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount
FROM [Order Details]
WHERE (OrderID = )
On the Where condition I want to say that Basically the OrderID is the same as the Original F1 Order ID.
How can I do that?
And After it is done Append the Result Row or Rows to my table1 Table.
How can I do it?
Best regards
Sanan
ASKER
Hi Pete
Thanks for reply.
What I want to do is simple; basically As I explained Before,
I have a form, which we Call it F1 and it’s Record Source is Q1 Query, I know how to send some Data from it to another (not related) Table Table1 using a Command Button and Some VB Coding in it’s OnClick Event, Her is the new Scenario, Also by pushing the same Button I want to be able to do what ever the Navigation Button does (add new data Button Off Course), which is Updatting my Original Q1 query (and Tables) and Moving to a New Record.
Or I can use the add new Button of Navigation Bar, to do what it supposed to do, also on top of it to add the all or some of data to my Extra Table (Table1).
Either of the above procedures would do it for me.
Back to your reply, are you telling me that this is not a good design, would you please explain why?
Best regards
Bijan
Thanks for reply.
What I want to do is simple; basically As I explained Before,
I have a form, which we Call it F1 and it’s Record Source is Q1 Query, I know how to send some Data from it to another (not related) Table Table1 using a Command Button and Some VB Coding in it’s OnClick Event, Her is the new Scenario, Also by pushing the same Button I want to be able to do what ever the Navigation Button does (add new data Button Off Course), which is Updatting my Original Q1 query (and Tables) and Moving to a New Record.
Or I can use the add new Button of Navigation Bar, to do what it supposed to do, also on top of it to add the all or some of data to my Extra Table (Table1).
Either of the above procedures would do it for me.
Back to your reply, are you telling me that this is not a good design, would you please explain why?
Best regards
Bijan
So, as I understand it, you have a bound form.
On the form you have a button which writes a record to a different table to the one the form is bound to - is that right?
You now want to save the the current record to its bound source and move to a new record- is that right?
If so then modify your code that writes to the second table to include:
Me.dirty = false
put this line before you write to the other table.
It will save the current record to the bound source.
After the code to write the other table put:
docmd.GoToRecord ,,acNewRec
This assumes that the code is in the form module and not in a standard module, otherwise you need
docmd.GoToRecord acDataForm,"formname",acNe
Obviousy I do not know anything about your application, but itis unusual to add the same data to two different tables.
It is inevitable that at some point the tables get out of line, because of user actions or program bugs.
But you may have good reasons for doing this.
Pete
On the form you have a button which writes a record to a different table to the one the form is bound to - is that right?
You now want to save the the current record to its bound source and move to a new record- is that right?
If so then modify your code that writes to the second table to include:
Me.dirty = false
put this line before you write to the other table.
It will save the current record to the bound source.
After the code to write the other table put:
docmd.GoToRecord ,,acNewRec
This assumes that the code is in the form module and not in a standard module, otherwise you need
docmd.GoToRecord acDataForm,"formname",acNe
Obviousy I do not know anything about your application, but itis unusual to add the same data to two different tables.
It is inevitable that at some point the tables get out of line, because of user actions or program bugs.
But you may have good reasons for doing this.
Pete
ASKER
Hi Peter57r
I sort of figuredout the first part of the question, Could youb help me on secon part of the Question, I sort of figure that one also, I just have a little problem with it which I will present it to you.
I want to send some Data from an access Form to a SQL Database Table (not related Table)
I have the following Codes which Works:
Private Sub Command95_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
myCn.ConnectionString = "DSN=TestE"
myCn.Open
myRs.Open "INSERT INTO TABLE1 VALUES (" & OrderID.Value & "," & OrderID.Value + 10000 & ",'" & CustomerID.Value & "','" & CompanyName.Value & "','" & OrderDate.Value & "'," & Tax1.Value & "," & Tax2.Value & "," & Total.Value & ");", myCn, adOpenDynamic
End Sub
But the Following Does not work:
Private Sub Command95_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
myCn.ConnectionString = "DSN=TestE"
myCn.Open
myRs.Open "INSERT INTO [dbo.OrderDetailsQoute] SELECT FROM [view.Order Details Extended] WHERE (OrderID=10036)", myCn, adOpenDynamic
End Sub
Off Course the actual WHERE Condition is
WHERE (OrdrerID=Forms!Qoute!Orde
Could some one tell me, why? (I think it should be in Punctuation Error in Insert Line.)
Best Regards
Bijan
I sort of figuredout the first part of the question, Could youb help me on secon part of the Question, I sort of figure that one also, I just have a little problem with it which I will present it to you.
I want to send some Data from an access Form to a SQL Database Table (not related Table)
I have the following Codes which Works:
Private Sub Command95_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
myCn.ConnectionString = "DSN=TestE"
myCn.Open
myRs.Open "INSERT INTO TABLE1 VALUES (" & OrderID.Value & "," & OrderID.Value + 10000 & ",'" & CustomerID.Value & "','" & CompanyName.Value & "','" & OrderDate.Value & "'," & Tax1.Value & "," & Tax2.Value & "," & Total.Value & ");", myCn, adOpenDynamic
End Sub
But the Following Does not work:
Private Sub Command95_Click()
Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
myCn.ConnectionString = "DSN=TestE"
myCn.Open
myRs.Open "INSERT INTO [dbo.OrderDetailsQoute] SELECT FROM [view.Order Details Extended] WHERE (OrderID=10036)", myCn, adOpenDynamic
End Sub
Off Course the actual WHERE Condition is
WHERE (OrdrerID=Forms!Qoute!Orde
Could some one tell me, why? (I think it should be in Punctuation Error in Insert Line.)
Best Regards
Bijan
ASKER
Hi Peter
How are you?
I am stillw aiting for you reply on my last question.
Best Regards
Bijan
How are you?
I am stillw aiting for you reply on my last question.
Best Regards
Bijan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Pete
I am sorry for delay.
But Thanks so much It worked just Fine.
Best regards
Bijan
I am sorry for delay.
But Thanks so much It worked just Fine.
Best regards
Bijan
The addnew button just positions you into an empty record it doesn't save a new (even empty) record. The record is not saved until you move away from it or otherwise do a save record action provided you have enetred some data.
The only event that will fire for the new record is the form_current event, although other events may fire to complete the processing of the record that was on the screen before you clicked the add new button.
Are you saying that you want to save the record that you are about to enter into 2 different tables?
This does not sound like good design, on the face of it.
Pete