Code to Add New Row in MS Access

[Riun]

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:

 
Private Sub New_Click()
 
Dim stDocName As String
 
stDocName = "INSERT INTO [Product Types2] ([Product Classification],[Front Office Product Type],[Risk Product Type]) VALUES ('" & ComboPC.Value & "','" & ComboFOPT.Value & "','" & txtRiskPT & "')"
DoCmd.RunSQL stDocName
 
End Sub
 
 

It works the first time, but not when you want to add the 2nd new row.

[MMcCarthy]

You need to requery the list box each time.

 
Private Sub New_Click()
Dim strSQL As String ' Just a naming convention
 
strSQL = "INSERT INTO [Product Types2] ([Product Classification],[Front Office Product Type],[Risk Product Type]) VALUES ('" & ComboPC.Value & "','" & ComboFOPT.Value & "','" & txtRiskPT & "')"
 
DoCmd.RunSQL strSQL
 
Me.ListBoxName.Requery
 
End Sub
 
 

[Riun]

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...

[MMcCarthy]

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?

[Riun]

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!

 Private Sub Combo51_BeforeUpdate(Cancel As Integer)
    txtBook.Value = Combo51.Value
    txtBook.Requery
End Sub
 
Private Sub New_Click()
'DoCmd.SetWarnings False
 
Dim strDisplay As String
Dim intResponse As Integer
Dim strPrompt As String
Dim stDocName As String
 
    If Focussed.Value = True Then strDisplay = "Yes"
    If Focussed.Value = False Then strDisplay = "No"
 
txtID = ""
If txtID = "" Or IsNull(txtID) Then
        stDocName = "INSERT INTO [Product Type p Portfolio2] (Book,[Front Office Product Type],[Focussed Area?]) VALUES ('" & txtBook.Value & "','" & [FO PT].Value & "','" & Focussed.Value & "')"
        DoCmd.RunSQL stDocName
        Me.List55.Requery
 
End If
 
End Sub
 

Don't know if this is the only answer, but it worked for now. Thank you for your help, MMcCarthy!

[ygdrasil24]

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!

 Private Sub Combo51_BeforeUpdate(Cancel As Integer)
    txtBook.Value = Combo51.Value
    txtBook.Requery
End Sub
 
Private Sub New_Click()
'DoCmd.SetWarnings False
 
Dim strDisplay As String
Dim intResponse As Integer
Dim strPrompt As String
Dim stDocName As String
 
    If Focussed.Value = True Then strDisplay = "Yes"
    If Focussed.Value = False Then strDisplay = "No"
 
txtID = ""
If txtID = "" Or IsNull(txtID) Then
        stDocName = "INSERT INTO [Product Type p Portfolio2] (Book,[Front Office Product Type],[Focussed Area?]) VALUES ('" & txtBook.Value & "','" & [FO PT].Value & "','" & Focussed.Value & "')"
        DoCmd.RunSQL stDocName
        Me.List55.Requery
 
End If
 
End Sub
 

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.!