By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,471 Members | 711 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,471 IT Pros & Developers. It's quick & easy.

Code to Add New Row in MS Access

P: 34
My Form has a List Box that need to be updated when someone type stuff in ComboPC,ComboFOPT&txtRiskPT. Also, the Table [Product Types2] needs to be updated.

I have a command button [New] with the following code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub New_Click()
  3.  
  4. Dim stDocName As String
  5.  
  6. stDocName = "INSERT INTO [Product Types2] ([Product Classification],[Front Office Product Type],[Risk Product Type]) VALUES ('" & ComboPC.Value & "','" & ComboFOPT.Value & "','" & txtRiskPT & "')"
  7. DoCmd.RunSQL stDocName
  8.  
  9. End Sub
  10.  
  11.  
It works the first time, but not when you want to add the 2nd new row.
Oct 24 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
You need to requery the list box each time.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub New_Click()
  3. Dim strSQL As String ' Just a naming convention
  4.  
  5. strSQL = "INSERT INTO [Product Types2] ([Product Classification],[Front Office Product Type],[Risk Product Type]) VALUES ('" & ComboPC.Value & "','" & ComboFOPT.Value & "','" & txtRiskPT & "')"
  6.  
  7. DoCmd.RunSQL strSQL
  8.  
  9. Me.ListBoxName.Requery
  10.  
  11. End Sub
  12.  
  13.  
Oct 25 '06 #2

P: 34
Thank you MMcCarthy, I appreciate it, but it still doesn't work. The first time the Form is opened and a row is added it works, and after that it doesn't want to add a new row. Any other suggestions, Please? One of my other forms with exactly the same code does work, though...
Oct 25 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thank you MMcCarthy, I appreciate it, but it still doesn't work. The first time the Form is opened and a row is added it works, and after that it doesn't want to add a new row. Any other suggestions, Please? One of my other forms with exactly the same code does work, though...
Is it possible that the problem lies in the underlying table rather than the list box. Are you trying to add data that would violate the unique index of a field. What is the table structure?
Oct 25 '06 #4

P: 34
my table has no indexes/primary keys. I found a solution, though: the difference between the firste forms (that works) and the second, was that the first reads the info to insert into table, out of text boxes, the second has one combo box to read from, thus, I wrote the value in the combo box to a text box, and from there, inserted it into the table!

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Combo51_BeforeUpdate(Cancel As Integer)
  2.     txtBook.Value = Combo51.Value
  3.     txtBook.Requery
  4. End Sub
  5.  
  6. Private Sub New_Click()
  7. 'DoCmd.SetWarnings False
  8.  
  9. Dim strDisplay As String
  10. Dim intResponse As Integer
  11. Dim strPrompt As String
  12. Dim stDocName As String
  13.  
  14.     If Focussed.Value = True Then strDisplay = "Yes"
  15.     If Focussed.Value = False Then strDisplay = "No"
  16.  
  17. txtID = ""
  18. If txtID = "" Or IsNull(txtID) Then
  19.         stDocName = "INSERT INTO [Product Type p Portfolio2] (Book,[Front Office Product Type],[Focussed Area?]) VALUES ('" & txtBook.Value & "','" & [FO PT].Value & "','" & Focussed.Value & "')"
  20.         DoCmd.RunSQL stDocName
  21.         Me.List55.Requery
  22.  
  23. End If
  24.  
  25. End Sub
  26.  
Don't know if this is the only answer, but it worked for now. Thank you for your help, MMcCarthy!
Oct 25 '06 #5

P: 1
my table has no indexes/primary keys. I found a solution, though: the difference between the firste forms (that works) and the second, was that the first reads the info to insert into table, out of text boxes, the second has one combo box to read from, thus, I wrote the value in the combo box to a text box, and from there, inserted it into the table!

Expand|Select|Wrap|Line Numbers
  1.  Private Sub Combo51_BeforeUpdate(Cancel As Integer)
  2.     txtBook.Value = Combo51.Value
  3.     txtBook.Requery
  4. End Sub
  5.  
  6. Private Sub New_Click()
  7. 'DoCmd.SetWarnings False
  8.  
  9. Dim strDisplay As String
  10. Dim intResponse As Integer
  11. Dim strPrompt As String
  12. Dim stDocName As String
  13.  
  14.     If Focussed.Value = True Then strDisplay = "Yes"
  15.     If Focussed.Value = False Then strDisplay = "No"
  16.  
  17. txtID = ""
  18. If txtID = "" Or IsNull(txtID) Then
  19.         stDocName = "INSERT INTO [Product Type p Portfolio2] (Book,[Front Office Product Type],[Focussed Area?]) VALUES ('" & txtBook.Value & "','" & [FO PT].Value & "','" & Focussed.Value & "')"
  20.         DoCmd.RunSQL stDocName
  21.         Me.List55.Requery
  22.  
  23. End If
  24.  
  25. End Sub
  26.  
Don't know if this is the only answer, but it worked for now. Thank you for your help, MMcCarthy!
____________________


This solution is not working for me, The tables are getting updated by what i put after single quotes ,

INSERT INTO CHILDPROJECT ( MASTERPROJECT, CHILDPROJECT )
VALUES (' " & mprj.value " ', ' " &chprj.value & " ');

this inserts "&mprj.value&" and " &chprj.value & "


Can u plz suggest some , its urgent.!
Jul 1 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.