How to save the data entered in textbox to a table.

[abhay_547]

Hi All,

I have sharepoint linked table table name "table1" in my access database and a form .i.e. form1 which has one combobox, one datepicker and one textbox which are linked to that sharepoint table in the following way, the combobox is linked to table column which is product column so the combobox gets populated with product names and for datepicker and textbox I have used a dlookup which populates datepicker with date of the commentary for the selected product and textbox with the commentary entered by product guy against selected product. Now I have command button which is "Submit commentary" on the form1. I want to give product guys this access form as an commentary input tool so that they don't need to go to the sharepoint link. They can straight away open the access form. Select their product in combobox and submit the commentary. Now I want to put some code in the background of the command button .i.e. submit commentary which will save back the commentary and the date selected in the datepicker by product guy against the product selected by him combobox to the sharepoint table which is a linked table in access. Note: the commentary is collected from products on daily basis so the whatever was entered by them yesterday will get populated today in the form1 so they just need to modify the same for today. Please help.

Thanks for your help in advance.

[abhay_547]

Hi All,

Did anyone get the chance to look into the above thread ?

Thanks a lot for your help in advance

[rpeare]

I don't use sharepoint but from what you describe sharepoint is actually storing the table, you have linked that table to Access (I'm assuming you have a unique identifier on the table). As long as you establish the unique identifier when you link the table you should be able to update and add records to the table as if it were a local access table and those records, once added/updated, should be immediately available on whatever other application you're using.

What exactly are you having trouble with, is your data not adding to your linked sharepoint table?
Or is a sharepoint 'table' actually stored as a text document?

[abhay_547]

Hi rpeare,

Thanks a lot for your reply. As mentioned earlier I just want input some code in the background of the submit command button which will save back the commentary updated in textbox and the date selected in the datepicker by product guy against the product selected by him combobox to the data table in access. Below is the code which I have so far. Please help.

Private Sub Combo2_AfterUpdate()
Me.DTPicker1.Value = DLookup("[Data As Of Date]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'")
Me.Text15 = DLookup("[Commentary]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'")

End Sub



Private Sub Command17_Click()

DLookup("[Commentary]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'") = Me.Text15.Value
DLookup("[Data As Of Date]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'") = Me.DTPicker1.Value

Thanks a lot for your help in advance

[abhay_547]

Hi All,

Did anyone get the chance to look into the above post ?

Thanks a lot for your help in advance

[rpeare]

Can you post a sample of your database, I'm getting lost in your description. Just make sure the data has no private information in it but enough data for us to understand your problem.

[abhay_547]

Hi rpeare,

Please find the attachment for my mdb file. I have removed my data and have added some dummy data in the place of same. Please have a look.

Thanks a lot for your help in advance

[rpeare]

Ok, now that I see your form I'd like to recap.

When you put in a product, you want to update the 'commentary' field and the 'data as of date' field.

if that's the case you don't want another pair of dlookups you want to run a sql statement that will update the appropriate product. I don't know why you have it set up this way but this is definitely NOT a normalized structure. In a normalized structure you'd have a product ID, a comment ID, the date the comment was made and the comment so one productID could have multipile comments on different days.

however, in order to do what you want you would want to build and execute a SQL statement:

in the ON CLICK event of command17 you'd have something like

dim sSQL as string

sSQL = "UPDATE [Commentary Table] SET "
sSQL = sSQL & "Commentary = '" & me.text15 & "',"
sSQL = sSQL & "[Data As Of Date] = #" & me.dtpicker1 & "# "
sSQL = sSQL & "WHERE (((ID)= " & me.combo2 & "))"

docmd.runsql sSQL

text15 is the name of the box where you have your commentary
dtpicker1 is the name of your date field on your form
combo2 is the combo box that has all your chosen product

[abhay_547]

Hi rpeare,

Thanks a lot for your quick reply, I have incorporated the code provided by you in the background of the Command17 button and I am facing the following error now.

Error Msg: Run-time error '3464': Data type mismatch in criteria expression

Actually post incorporating the code provided by you in command17 click event I had observed following things that when I updated the commentary in text15 box and clicked on the button it was showing some warning messages so to ignore those warning msgs I had set the warnings to off and start of click event and then set it On again at last but still I was facing an error like Syntax error (missing operator) in query expression '(((ID)=FI Cash))', I tried to investigate the error and had identified that when I select those products which had spaces in their names I was getting such error but if I select a product which didn't have space in between it's name it was working. for e.g. I have product like "DCM" and "FI Cash" in combobox dropdown If I select DCM it works fine but if I select "FI Cash" it throws above mentioned syntax error. To get rid of this syntax error I added Apostrophe / Single Quote in this line .i.e. '" & Me.Combo2 & "'))" so post that if I select it starting showing the data type mismatch in criteria expression.

Apart from the above error I need help on one more thing .i.e. in my database table .i.e. commentary table I have column called "Expected Data As of Date" now this column has those dates which the data provider is supposed to select in "Data As of Date" .i.e. DTPicker while submitting the commentary I want to put some code in the click event of the command17 which will look for the "Expected Data As of Date" for the product selected in the combobox and then try to match it with the date which selected by the user in the DTpicker and if it doesn't match then it will show a msgbox that the date selected by user is incorrect and he need to select the correct date. I tried to do the same by using dlookup .i.e. If Me.Dtpicker1.value <> dlookup("[Expected data as of date]"..... then show msgbox at the end of the clickevent code but unfortunately it didn't work. Can you please help me with the same as well.

Private Sub Command17_Click()
DoCmd.SetWarnings WarningsOff
 
 
Dim sSQL As String
 
sSQL = "UPDATE [Commentary Table] SET "
sSQL = sSQL & "[Commentary] = '" & Me.Text15 & "',"
sSQL = sSQL & "[Data As Of Date] = #" & Me.DTPicker1 & "# "
sSQL = sSQL & "WHERE (((ID)= '" & Me.Combo2 & "'))"
 
DoCmd.RunSQL sSQL
DoCmd.SetWarnings WarningsOn
 
 
'If Me.DTPicker1.Value <> DLookup("[Expected Data As of Date]", "[Commentary Table]", "[Product] = '" & Me.Combo2 & "'") Then
'MsgBox "The Commentary Date selected doesn't meet the expected Timeline. Please Select the Correct date"
'Else
'MsgBox "Commentary Submitted Successfully"
'End If
End Sub

Thanks a lot for your help in advance

[rpeare]

I've simplified your form. You don't need to hit the button to save, every change you make is automatically saved.

[abhay_547]

Hi rpeare,

Thanks a lot for your quick reply and Apologies as I can't use the new change suggested by you .i.e. automatically saving the commentary without a save button the reason is the data providers are quite used to make changes to the commentary end number of times and they also go back to the old commentary any point of time when they are on form. So we want to give them an option to submit the commentary and as of date when they think it's final. Apologies again but We don't want to change this logic of submission as of now. So I would request you to help me with me with the issue which I have mentioned in my earlier post as we are almost done now and are left with a bit and once it's fixed my current form should be up and running.

Enclosed is my latest mdb file.

Thanks a lot for your help in advance

[abhay_547]

Hi rpeare,

Did you get the chance to look into the above post ?


Thanks a lot for your help in advance

[abhay_547]

Hi rpeare,

Did you get the chance to look into the above post ?


Thanks a lot for your help in advance

[rpeare]

sSQL = "UPDATE [Commentary Table] SET "
sSQL = sSQL & "[Commentary] = '" & Me.Text15 & "',"
sSQL = sSQL & "[Data As Of Date] = #" & Me.DTPicker1 & "# "
sSQL = sSQL & "WHERE (((ID)= '" & Me.Combo2 & "'))"

This is your SQL statement.

The problem is that your ID field in your COMMENTARY TABLE is an autonumber field and you are trying to insert a value based on a text value in that field.

In order to make this work you need to do one of two things.

1. Change your combo box so that the ID field comes first, set your combo box to have two columns, make the first column width 0 (so it won't be visible and your form will operate as it does now) Then change your WHERE clause to this:

sSQL = sSQL & "WHERE (((ID)= " & Me.Combo2 & "))"

removing the ' marks which indicate you're looking up a text value.

2. Change your SQL WHERE clause to be:

sSQL = sSQL & "WHERE (((PRODUCT)= " & Me.Combo2 & "))"

Personally I would do it with the ID but the choice is up to you.