Transferring form data to table? (1 Viewer)

Robert88 · Feb 23, 2006

Hi All,

I need help getting data from a group of unbound fields on a form to a table.

Is this possible? Hopefully with VBA.

Example of fields on form - frmLabel1Data

txtClientName
txtProjectNumber
txtTypeofMedia
txtPath1Files
txtFile1NameFrom

Example of fields in table - tblCDLabels

ClientName
ProjectNumber
TypeofMedia
Path1Files
File1NameFrom

As I am only new to this VBA any help will be appreciated.

Robert88

pono1 · Feb 23, 2006

Robert,

Roughly, the syntax is along these lines...

Code:

DoCmd.RunSQL _
"INSERT INTO TblCDLABELs (ClientName, ProjectNumber,Etc)
VALUES(txtClientName,txtProjectNumber, txtEtc)"

One catch: Access VBA is very touchy when it comes to specifying strings and numbers with quotes and apostrophes in SQL statements, so search the forum for examples. Also, search on Insert Into Statement and Append Query in the Access Help file for even more examples.

For starters, try to insert just one field, then, once that's working, continue to build up your SQL string piece by piece...

Regards,
Tim

Robert88 · Feb 23, 2006

Hi Tim,

Thank you, very much appreciated.

If I have any problems I will let you know but shouldn't with this example.

Robert88

Robert88 · Feb 23, 2006

Hi All,

I have realised that;

Code:

DoCmd.RunSQL _
"INSERT INTO TblCDLABELs (ClientName, ProjectNumber,Etc)
VALUES(txtClientName,txtProjectNumber, txtEtc)"

is not a good solution as I am trying to place this into a table whereby I have an additional field on the form txtLabelNumber and also have a field in the table fldLabelNumber, whereby the table has six records and I need to populate the relevant recordset WHERE fldLabelNumber = txtLabelNumber.

Rather than append the information, what SQL should be used as I tried it with a WHERE statement on the end of the code above but it did not work.

Then I tried to look for REPLACE in help with no luck. I gather there is an SQL statement that will handle this but not sure.

If anyone can help I would appreciate it.

Robert88

Pat Hartman · Feb 23, 2006

What is wrong with using bound forms? When you use unbound forms, YOU are responsible for writing all the code necessary to handle recordset navigation, adding records, updating records, and deleting records. With a bound form NO code is required for these things. Access handles it all.

Robert88 · Feb 23, 2006

Hi Pat,

I have three tables of information that is loaded into the form upon open. The tables are not related to each other.

Sometimes a table is not present after it is read form a CD.

I can see where you are going with a bound form but since all three tables are not related it makes it hard to bind the tables togehter to attain the information for one query in order to make it bound in order to update directly to a table via a query.

The data I am using to give you an idea is Folderpath, filename, file size from a scan of the CD. One other Fox Pro database has information in relation to the clients software. Along with the last Fox Pro database relating to the project.

Since there is no relationship between the CD sacnned data and other I thought that unbound would be better since the label I make contains data from all three.

If I am wrong, please advise.

Robert88

Ashfaque · Feb 23, 2006

Robert,

Mr. Pat is right. I also suggest you to use bound forms. I was using before unbound forms and transfering records to table upon clicking a command button. If you need like that, try following steps.

First, make sure your table field fldLabelNumber is indexed / Unique when you update the record, the information must go to the proper recordset. I hope you like your fldLabelNumber not to be duplicate.

Let us assume that your field name fldLabelNumber has index name called "RobIndex"...OK

Now create a button on the form and write following code on it OnClick event and play with it according to your need.

Option Compare Database
Dim db as DataBase
Dim rs as RecordSet
Dim frm as Form

Private Sub YourBtnName()
Set db = CurrentDb() 'keeping in mind your table is within the same database
Set rs = db.OpenRecordset("Your table name where data goes")
rs.Index = "RobIndex"
rs.Seek "=", Me![txtLabelNumber] ' This will check label number
If rs.NoMatch Then
MsgBox "This lbl number is not available"
rs.AddNew
rs!fldLabelNumber = txtLabelNumber
rs!ClientName=txtClientName
rs!ClientName=txtClientName
rs!ProjectNumber=txtProjectNumber
rs!TypeofMedia=txtTypeofMedia
rs!Path1Files=txtPath1Files
rs!File1NameFrom=txtFile1NameFrom
rs.Update
rs.CLOSE
Me.Refresh
Else ' It mean record is already available and need to be updated

rs.Edit
rs!ClientName=txtClientName
rs!ClientName=txtClientName
rs!ProjectNumber=txtProjectNumber
rs!TypeofMedia=txtTypeofMedia
rs!Path1Files=txtPath1Files
rs!File1NameFrom=txtFile1NameFrom
MsgBox "RECORD SUCCESSFULLY UPDATED", vbOKOnly, "SAVE INFORMATION"
rs.Update
rs.CLOSE
End If

Hopefully it would help.

With kind regards,
Ashfaque
ashfaque_online@yahoo.com

Ashfaque · Feb 23, 2006

Robert,

Mr. Pat is right. I also suggest you to use bound forms. I was using before unbound forms and transfering records to table upon clicking a command button. If you need like that, try following steps.

First, make sure your table field fldLabelNumber is indexed / Unique when you update the record, the information must go to the proper recordset. I hope you like your fldLabelNumber not to be duplicate.

Let us assume that your field name fldLabelNumber has index name called "RobIndex"...OK

Now create a button on the form and write following code on it OnClick event and play with it according to your need.

Option Compare Database
Dim db as DataBase
Dim rs as RecordSet
Dim frm as Form

Private Sub YourBtnName()
Set db = CurrentDb() 'keeping in mind your table is within the same database
Set rs = db.OpenRecordset("Your table name where data goes")
rs.Index = "RobIndex"
rs.Seek "=", Me![txtLabelNumber] ' This will check label number
If rs.NoMatch Then
MsgBox "This lbl number is not available"
rs.AddNew
rs!fldLabelNumber = txtLabelNumber
rs!ClientName=txtClientName
rs!ClientName=txtClientName
rs!ProjectNumber=txtProjectNumber
rs!TypeofMedia=txtTypeofMedia
rs!Path1Files=txtPath1Files
rs!File1NameFrom=txtFile1NameFrom
rs.Update
rs.CLOSE
Me.Refresh
Else ' It mean record is already available and need to be updated only

rs.Edit
rs!ClientName=txtClientName
rs!ClientName=txtClientName
rs!ProjectNumber=txtProjectNumber
rs!TypeofMedia=txtTypeofMedia
rs!Path1Files=txtPath1Files
rs!File1NameFrom=txtFile1NameFrom
MsgBox "RECORD SUCCESSFULLY UPDATED", vbOKOnly, "SAVE INFORMATION"
rs.Update
rs.CLOSE
End If

Hopefully it would help.

With kind regards,
Ashfaque
ashfaque_online@yahoo.com

Rich · Feb 23, 2006

That's Mrs. Pat, by the way

Robert88 · Feb 23, 2006

Hi Everybody who is helping here,

Thank you, I appreciate it. I am also learning and take on board your comments.

OK, so now I would like to know how to make this label form frmLabel1Data with a bound field as you have suggested? I am obviously going to have to make changes. Now this is where it gets hard for me as I do not want to reveal company info so I hope the example tables reflect what I am doing.

I suppose it is like building a house, get the foundations incorrect and rest is built on top of it, hhhmmmmm.....

Tables;

tblFilename 'This table is guarnteed from a scan of each CD.
fldFieldname 'This field has folder path and filename.
fldFileSize 'this filed contains the file size in bytes.

tblDBF1 'This table relates to the specifics of a project but not always on each CD with no unique ID field and can contain many records.
client
ProjectNumber
country
from
to

tblDBF2 'This table relates to the specifics of a project and is also on one of five different types of CD I need to label. Usually it has only one record.
client
ProjectNumber
country
from
to

One of the CD's have neither of the above 2 DBF's on board and this is where the user is required to make additional entry's. Even a scan of the CD gives no suggestion to client or project.

tblCDLabels 'This table is the table I was using to store the info after reading data from tables that are possibly there and then users input could change it to then update it to this table.
ClientName
ProjectNumber
TypeofMedia 'combobox with list in table only contains CD or DVD
Path1Files 'to be used to describe on certain CD's a particular path of a file
File1NameFrom 'from the above dbf's from field
File1NameTo 'from the above dbf's to field

I personally cannot see how this is going to be bound to a form but then again I am learning and happy for any suggestions.

Os is it just simply a matter of making it bound to tblCDlabels or a query there from?

Robert88

Ashfaque · Feb 25, 2006

Thanks Rich

And sorry Pat for the wrong salutation.

With kind regards,
Ashfaque

Robert88 · Feb 27, 2006

Hi Ashfaque,

Please clarify this

Let us assume that your field name fldLabelNumber has index name called "RobIndex"...OK

Code:

rs.Index = "RobIndex"

My Code stops at this location and I do not understand what you are trying to do here. I do not have any index as far as I am aware and not sure how to set it?

Look forward to some explaination.

Robert88

Robert88 · Feb 27, 2006

Hi all,

Since nobody is helping with a method that is new, I have reverted to the first suggestion but with an UPDATE SQL.

Seem to be having a few problems executing it, keeps giving me errors. Can anybody see the error?

Code:

Private Sub CmdUpdateCDLabelTable_Click()

  Dim strSQL As String
        strSQL = "UPDATE tblCDlabels SET tblCDlabels.fldDate = """ _
	& [Forms]![frmLabel1Data]![txtDate] & """" & tblCDlabels.fldClientName = """ _
	& [forms]![frmLabel1Data]![txtClientname] & """ & tblCDlabels.fldCDID = """ _
	& [forms]![frmLabel1Data]![txtCDID] & """ & tblCDlabels.fldProjectNumber = """ _
	& [forms]![frmLabel1Data]![txtProjectNumber] & """ & tblCDlabels.fldTypeofMedia = """ _
	& [forms]![frmLabel1Data]![txtTypeofMedia] & """ & tblCDlabels.fldPath1Files = """ _
	& [forms]![frmLabel1Data]![txtPath1Files] & """ & tblCDlabels.fldPath2Files = """ _
	& [forms]![frmLabel1Data]![txtPath2Files] & """ & tblCDlabels.fldPath3Files = """ _
	& [forms]![frmLabel1Data]![txtPath3Files] & """ & tblCDlabels.fldPath4Files = """ _
	& [forms]![frmLabel1Data]![txtPath4Files] & """ & tblCDlabels.fldPath5Files = """ _
	& [forms]![frmLabel1Data]![txtPath5Files] & """ & tblCDlabels.fldPath6Files = """ _
	& [forms]![frmLabel1Data]![txtPath6Files] & """ & tblCDlabels.fldFile1NamesFrom = """ _
	& [forms]![frmLabel1Data]![txtFile1NameFrom] & """ & tblCDlabels.fldFile2NamesFrom = """ _
	& [forms]![frmLabel1Data]![txtFile2NameFrom] & """ & tblCDlabels.fldFile3NamesFrom = """ _
	& [forms]![frmLabel1Data]![txtFile3NameFrom] & """ & tblCDlabels.fldFile1NamesTo = """ _
	& [forms]![frmLabel1Data]![txtFile1NameTo] & """ & tblCDlabels.fldFile2NamesTo = """ _
	& [forms]![frmLabel1Data]![txtFile2NameTo] & """ & tblCDlabels.fldFile3NamesTo = """ _
	& [forms]![frmLabel1Data]![txtFile3NameTo] & """ & WHERE (tblCDlabels.fldLabelNumber) = " _
	& [Forms]![frmLabel1Data]![txtLabelNumber] & ";"

    DoCmd.RunSQL strSQL

End Sub

Look forward to anyones response to check this.

The file below, requires you to open form frmMediaLabeller, then select button "Edit Label" opening the form frmLabel1Data and then select button "Update Data to Table" which executes the code in the window above.

Robert88

Pat Hartman · Feb 27, 2006

1. You don't say what error you are getting. If it is a type mismatch, remove the quotes from any date or numeric fields in the SQL string.

2. You haven't delimited the sets with commas.

3. Rather than using the full name to reference field objects, use the Me.fldname syntax if they are on the form from which the code is running.

Code:

  Dim strSQL As String
        strSQL = "UPDATE tblCDlabels SET tblCDlabels.fldDate = #" _
	& Me.txtDate & "#, tblCDlabels.fldClientName = """ _
	& Me.txtClientname & """, tblCDlabels.fldCDID = " _
	& Me.txtCDID & ", tblCDlabels.fldProjectNumber = """ _
...

Put a stop in the code at the runSQL line and print out the SQL string or paste it into the query window to test it.
I made the assumption that txtDate was a date field and that fldCDID was numeric.

Robert88 · Feb 28, 2006

Hi Pat,

I have tried everything in your email. Reduced the fields that are taking information so as not to deal with as much info so as to get a few working before I tackle the complete set.

I appreciate your help but I am still getting errors.

My code now reads;

Code:

Private Sub CmdUpdateCDLabelTable_Click()

  Dim strSQL As String
    strSQL = "UPDATE tblCDlabels SET tblCDlabels.fldDate = #" _
            & Me.txtDate & "#, tblCDlabels.fldClientName = """ _
            & Me.txtClientName & """, tblCDlabels.fldCDID = """ _
            & Me.txtCDID & """, tblCDlabels.fldProjectNumber = """ _
            & Me.txtProjectNumber & """, tblCDlabels.fldPath1Files = """ _
            & Me.txtPath1Files & """, tblCDlabels.fldTypeofMedia = """ _
            & Me.txtTypeofMedia & """, WHERE (((tblCDlabels.fldLabelNumber)=[frmLabel1Data].[txtLabelNumber]));"

        [COLOR="Red"]DoCmd.RunSQL strSQL[/COLOR]

End Sub

This generates a Run-time error '3144'; Syntax error in UPDATE Statement. The code stops on the red code above when it jumps to the Visual Basic Editor on this error.

I have also attached a narrowed down version of this database so that you can see it for your self like I did in the last posting.

I hope someone can help.

Robert88

Pat Hartman · Feb 28, 2006

You have an extra comma just before the where clause. Did you print out the SQL string as I suggested? It is very difficult to evaluate the multiple quotes and concatenated strings by reading the SQL string as it is being built.

Robert88 · Feb 28, 2006

Hi Pat,

I did try pasting it into a query window with no luck.

I have tried again and like a parameter query a window pops up requesting frmLabel1Data.txtLabelNumber.

I also tried Me.txtLabelNumber with same result, by the way the form frmLabel1Data is open whilst I try this.

It seems the SQL is not working.

The only thing that I can think that is causing this is that when I go into the "Edit Label" button form frmMediaLabeller form, using a variable I populate the txtLabelNumber field. Not sure why the query is not picking up this field........

Any idea?

Robert88

Robert88 · Feb 28, 2006

It works but I still do not understand why it only accepts a number?

Hi Pat,

This solution is crazy but in this case it is ok for me.

The only thing I replaced was the "1" shown red, I tried all of the other options with no luck below;

1. """ & frmLabel1Data.txtLabelNumber & """
2. frmLabel1Data.txtLabelNumber
3. [frmLabel1Data].[txtLabelNumber]

Code:

Private Sub CmdUpdateCDLabelTable_Click()

  Dim strSQL As String
    strSQL = "UPDATE tblCDlabels SET tblCDlabels.fldDate = #" _
            & Me.txtDate & "#, tblCDlabels.fldClientName = """ _
            & Me.txtClientName & """, tblCDlabels.fldCDID = """ _
            & Me.txtCDID & """, tblCDlabels.fldProjectNumber = """ _
            & Me.txtProjectNumber & """, tblCDlabels.fldPath1Files = """ _
            & Me.txtPath1Files & """, tblCDlabels.fldTypeofMedia = """ _
            & Me.txtTypeofMedia & """ WHERE (((tblCDlabels.fldLabelNumber)= [B][COLOR="Red"]1[/COLOR][/B]));"

    DoCmd.RunSQL strSQL

End Sub

Since I am trying to place 6 labels on an A4 page this solution is fine for me as each Label of 6 has its own form. But it has got me bugged why only a number works and any reference to the field does not???????

When the parameter query pops up and you place a "1" in it, it works. otherwise it does not??????????????

I would still love to know why. As much as I have been banging my head it has twisted my learning as to how these queries should work

Anyway if anyone can explain I would appreciate it.

Otherwise thank you everyone who has helped along the way with this problem.

Robert88

Pat Hartman · Mar 1, 2006

Sorry, the Where was off the screen and I didn't scroll far enough.

& Me.txtTypeofMedia & """ WHERE (((tblCDlabels.fldLabelNumber)= " & Me.txtLabelNumber & "));"

Ashfaque · Mar 1, 2006

Robert,

Bring the table into design mode and see the attached file. Index name can be any name. When you are in desing mode of the table, click the index icon and a dialog box would open and then select that field name. At the end type the name of your choice as index name (I had typed 'Robindex'). If you type name of your choice, you need to put the same name in code also. Run then.

Hope it helps.

Regards,
Ashfaque

Transferring form data to table? (1 Viewer)

Robbie

Registered User.

Robbie

Robbie

Super Moderator

Robbie

Student

Student

Rich

Guest

Robbie

Student

Robbie

Robbie

Attachments

Super Moderator

Robbie

Attachments

Super Moderator

Robbie

Robbie

Super Moderator

Student

Attachments

Similar threads

Users who are viewing this thread