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

Writing records to a seperate table from a form

P: 22
Ok I have a form which calls from a few different tables and you use the drop down box to select your options which then shows what you have selected in 4 text boxes in the form each text box is the result from each of the drop down menus.

Now I want to have a button that when clicked will take the values from those 4 text boxes and write it to a table and export the file to a tab delimited text file?

If anyone could help me or point me in the right direction it would be greatly appreciated.

Cheers
Nov 29 '06 #1
Share this Question
Share on Google+
42 Replies


NeoPa
Expert Mod 15k+
P: 31,660
Ok I have a form which calls from a few different tables and you use the drop down box to select your options which then shows what you have selected in 4 text boxes in the form each text box is the result from each of the drop down menus.
You don't want to read that when you're tired or drunk!
I'll look again tomorrow to see if I can make any sense of it.
Nov 29 '06 #2

P: 22
Yeah I just re-read it myself it's pretty confusing!

What I have on my form is 4 combo boxes each calling a different table to get different information. Now at the footer of the form is a set of 4 text boxes the first text box displays the option you chose in the first combo box, the second text box displays the option you chose in the second combo box etc etc. I want to be able to then take the four values in those text boxes and add it to a table via the click of the button.

Hope that makes a bit more sense.

Cheers
Nov 29 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
Either it makes more sense or I'm more awake ;).
Firstly, you probably don't need the TextBoxes. The value of a ComboBox is (generally - unless you have a more complicated, column based one) the selected item.
Secondly, you will need to write code to add them to a table.
You can create a string in your code that produces a SQL command to execute (INSERT INTO ...) or you can write code to use ADODB or DAO functions and objects to add the data in.
Nov 29 '06 #4

P: 22
Hi thanks NeoPa I will check out the sql stuff.
What you select in the combo box shows a different result in the text box as for example in the first combo box the user selects a course name and in the 1st text box the course ID shows up so I need those values in the text box to write back to a table, all the combo boxes are the user friendly version of what really is needed in the text boxes. Is this still possible to write those values using sql INSERT INTO?
Nov 29 '06 #5

blyxx86
100+
P: 256
Hi thanks NeoPa I will check out the sql stuff.
What you select in the combo box shows a different result in the text box as for example in the first combo box the user selects a course name and in the 1st text box the course ID shows up so I need those values in the text box to write back to a table, all the combo boxes are the user friendly version of what really is needed in the text boxes. Is this still possible to write those values using sql INSERT INTO?
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO tblName(field1, field2, field3) Values([formfield],[formfield2],[formfield3])"
  3. DoCmd.RunSQL (strSQL)
  4.  
That worked for me, to insert new values to a table as a new record. The code may be a bit different.
Nov 29 '06 #6

P: 22
Thanks I tried your code my came out like this:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) Values([txtSectionID],[txtUserName],[txtEnrollRemove],[txtRole])"
  3. DoCmd.RunSQL (strSQL)
Is this the correct way to read values from text boxes? Access gives me the warning that it is about to append some data to the table blah blah. It then comes up with an error saying access can't append the records. Then goes on to say that it set 0 fields to null due to a type conversion failure, didn't add 1 record due to key violations, 0 records due to lock violations, 0 records due to validation rule violations???
Nov 29 '06 #7

blyxx86
100+
P: 256
Thanks I tried your code my came out like this:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) Values([txtSectionID],[txtUserName],[txtEnrollRemove],[txtRole])"
  3. DoCmd.RunSQL (strSQL)
Is this the correct way to read values from text boxes? Access gives me the warning that it is about to append some data to the table blah blah. It then comes up with an error saying access can't append the records. Then goes on to say that it set 0 fields to null due to a type conversion failure, didn't add 1 record due to key violations, 0 records due to lock violations, 0 records due to validation rule violations???
I may have oopsied on the code, let me get you a better one..

Yes, i put it within brackets, should be within single quotes.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) Values('txtSectionID','txtUserName','txtEnrollRemove','txtRole')"
  3. DoCmd.RunSQL (strSQL)
You can set the warning dialogs to false, with some code, but I can't remember off the top of my head. You would put something like Set.Warnings = False and then after the end of the code Set.Warnings = True so it turns back on. I don't really know of any other way to turn off the warning messages about appending data (there is also an option in the preferences, but turning them off and on is the best way.)

**Watch the code i sent, it's not quite showing up right for me.**
Nov 29 '06 #8

P: 22
I tried using the [ and ' for the value fields but it still gives me the same error mentioned in my last post, if I click no when the error comes up it says Run-time error '2501':
The RunSQL action was canceled. If I click debug it highlights DoCmd.RunSQL (strSQL) in yellow in my code?

Cheers
Nov 30 '06 #9

NeoPa
Expert Mod 15k+
P: 31,660
Thanks I tried your code my came out like this:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) Values([txtSectionID],[txtUserName],[txtEnrollRemove],[txtRole])"
  3. DoCmd.RunSQL (strSQL)
Is this the correct way to read values from text boxes? Access gives me the warning that it is about to append some data to the table blah blah. It then comes up with an error saying access can't append the records. Then goes on to say that it set 0 fields to null due to a type conversion failure, didn't add 1 record due to key violations, 0 records due to lock violations, 0 records due to validation rule violations???
That's not quite the right format.
Remember, the VBA code can see the form, but the SQL interpreter (or engine) has no way of knowing which fields you're referring to. Hence the error message which basically said "You haven't given me any values - what do you expect me to do?"
you need to build up the string in the code (which knows the values) and pass it to SQL with literal values in the string.
I've assumed txtSectionID is numeric and all the rest are strings. I've also used the SELECT version (I'm not familiar with the VALUES clause) as this is what I've tested.
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) " & _
  3.     "VALUES(" & [txtSectionID] & ",'" & [txtUserName] & _
  4.     "','" & [txtEnrollRemove] & "','" & [txtRole]"')"
  5. DoCmd.RunSQL (strSQL)
If the four values are 1; A; B; C; then strSQL will be :
INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) VALUES(1,'A','B','C')
this is what will be passed to the SQL interpreter using DoCmd.RunSQL.
Nov 30 '06 #10

P: 22
Cheers for that, the txtSectionID and UserName are both alphanumeric values and the txtEnrollRemove and txtRole a single digit numbers.

When I put in this code it comes up red in the code view and highlights ")" and brings up the Compile Error: Expected: end of statement
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) " & _
  2.     "VALUES('" & [txtSectionID] & "','" & [txtUserName] & _
  3.     "'," & [txtEnrollRemove] & "," & [txtRole]")"
What exactly am I typing wrong?

Cheers
Nov 30 '06 #11

NeoPa
Expert Mod 15k+
P: 31,660
Cheers for that, the txtSectionID and UserName are both alphanumeric values and the txtEnrollRemove and txtRole a single digit numbers.

When I put in this code it comes up red in the code view and highlights ")" and brings up the Compile Error: Expected: end of statement
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) " & _
  2.     "VALUES('" & [txtSectionID] & "','" & [txtUserName] & _
  3.     "'," & [txtEnrollRemove] & "," & [txtRole]")"
What exactly am I typing wrong?

Cheers
Exactly? You have no & before your last string literal (after [txtRole]).
Try :
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) " & _
  2.     "VALUES('" & [txtSectionID] & "','" & [txtUserName] & _
  3.     "'," & [txtEnrollRemove] & "," & [txtRole] & ")"
Nov 30 '06 #12

P: 22
Ok the end of statement error doesn't come out now cheers.

But that same error when it's about to append the data to the table still comes up??? This is the full code I have assigned to the button on click event:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnEnrollment_Click()
  2.     Dim strSQL As String
  3.     strSQL = "INSERT INTO Enrollment(SectionID, UserName, AddRemove, RoleType) " & _
  4.     "VALUES('" & [txtSectionID] & "','" & [txtUserName] & _
  5.     "'," & [txtEnrollRemove] & "," & [txtRole] & ")"
  6.     DoCmd.RunSQL (strSQL)
  7. End Sub
Nov 30 '06 #13

P: 22
I figured out why the error comes up when it tries to append to the table I had 2 entries in there already so I deleted them and tried the button it wrote to the table fine, hooray! A new dilemma has showed itself it won't actually add a new record if there is a record in there already??? How can I add more records as I go, so the database will continually get bigger, and how would I detect duplicates?

Cheers
Nov 30 '06 #14

NeoPa
Expert Mod 15k+
P: 31,660
That depends on the design of your database (your table in particular).
If you have a PK set (or any unique index) then you will not be able to add another record with the same value in that index.
Nov 30 '06 #15

P: 22
Ah cheers. I re-created the Enrollment table and didn't assign a PK this time, and it works great now!

Now how would I go about checking if there is duplicate entries?

Another question is I want to be able to export the data to a tab delimited text, I have a button which runs a macro that has the TransferText action with the Transfer Type set to export delimited but it comes out as a comma delimited file, how can change that to tab delimited and not have quotes around strings?
Also I tried using the ApplyFilter action to only export entries that have only been added on the current date, the table has a field at the end called DateAdded with the default value set to =Date() how can I filter out any dates that don't match today and have the filename have todays date added to the end so as not to overwrite the older exports?

Thanks for all your help so far!

Cheers
Nov 30 '06 #16

P: 22
Figured out how to set the TransferText Action to tab just had to run the export manually and create a specification type through the advanced menu.

Now all I need help on is how to filter the exports based on date and assign the date to the end of the file name so a user can export all the users they added that day rather than exporting the whole table, aswell as being able to detect duplicates in the tables?

Cheers
Dec 1 '06 #17

NeoPa
Expert Mod 15k+
P: 31,660
When you use TransferText you specify a data source which must be a Table or a QueryDef (saved query).
You need to make sure there is a QueryDef with the correct selection criteria available.

Duplicates shouldn't be in the table in the first place :(.
If you're talking about duplicates of the PK then don't worry, there can't be any. Access will never allow that to happen.
If not of the PK then explain what you mean by duplicates and then we can see what can be done to help.
Dec 1 '06 #18

P: 22
Cheers didn't realise you could put a query in the macro instead of a table, works a treat.

As for the duplicates when a user opens the enrollment form and selects all the options from the four combo boxes the Add enrollment buttons takes all the value from the four text boxes and add its to the enrollment table.
How run a check on the Enrollment table comparing the 4 text boxes to entries in the table before it is added and spit out an error message if the user has already been enrolled in that course? For it to be a duplicate all 4 columns have to match so as not to enroll someone twice.

Cheers
Dec 4 '06 #19

NeoPa
Expert Mod 15k+
P: 31,660
If you want Access to throw an error (which can be handled and acted upon) then simply define a unique key which includes all fields then use On Error to handle the situation.
Otherwise, before writing it away do something like :
Expand|Select|Wrap|Line Numbers
  1. Dim strWhere As String
  2.  
  3. strWhere = "([SectionID] & [UserName] & " & _
  4.            "[AddRemove] & [RoleType] = '" & _
  5.            [txtSectionID] & [txtUserName] & _
  6.            [txtEnrollRemove] & [txtRole] & _
  7.            "')"
  8. If IsNull(DLookup("[SectionID]", _
  9.                   "[Enrollment]", _
  10.                   strWhere)) Then _
  11.     'Write the record
  12. Else
  13.     MsgBox "Duplicate"
  14. End If
PS The line-continuations (_) are not so necessary in your code. I include them for readability in TheScripts.
Dec 4 '06 #20

P: 22
NeoPa you sir are a god!

Cheers for all your help, my database is functioning beautifully now.

One last thing that I could annoy you with is with the export of the table information is the date field, how could I strip that field out after the query has sorted that field so the exported text file doesn't contain the date field?

Cheers
Dec 4 '06 #21

NeoPa
Expert Mod 15k+
P: 31,660
Send (post) me your SQL and I will look at it for you.
Generally, though, you simply omit it from the SELECT list.
Dec 4 '06 #22

P: 22



I tried unticking the DateAdded field to not be shown but to still be sorted by the date, it works fine if I run the query in Access directly but as soon as I try the button on the form for the macro to run the query it fails to export the tab delimited file. As soon as I tick show for the date field in the query the export works fine?

Cheers
Dec 4 '06 #23

NeoPa
Expert Mod 15k+
P: 31,660
If your problem is with exporting the query (that works when run directly) then you need to post the code you use to execute the export instead.
Sorry, but your question was different before.
It may be that it won't be able to sort by a field that's not included in the display list.
Don't forget to include the exact error message and the line of your code that it occurrs on.
Dec 4 '06 #24

NeoPa
Expert Mod 15k+
P: 31,660
Looking at your posted query, I notice you sort on the date AS WELL AS selecting only the current date.
These two things together don't really make sense. Why would you sort on a single value?
This shouldn't cause any problems exactly, it's just not required.

BTW to post the SQL for that query, simply select View / SQL View and copy the SQL code you find. That is the equivalent of the query as displayed.
Dec 4 '06 #25

P: 22
Heres the sql code for the query:

Expand|Select|Wrap|Line Numbers
  1. SELECT StudentCreation.UserName, StudentCreation.FirstName, StudentCreation.LastName, StudentCreation.Email, StudentCreation.Password
  2. FROM StudentCreation
  3. WHERE (((StudentCreation.DateAdded)=Date()));
Dec 4 '06 #26

NeoPa
Expert Mod 15k+
P: 31,660
If your problem is with exporting the query (that works when run directly) then you need to post the code you use to execute the export instead.
Sorry, but your question was different before.
It may be that it won't be able to sort by a field that's not included in the display list.
Don't forget to include the exact error message and the line of your code that it occurrs on.
Ahrjay,

Misunderstanding here.
Your post of the picture of the design was fine. I was just giving you an easier method for next time (And I've just noticed - you've taken out the sort - I saw you ;)).
What I need now from you is from the earlier post (#24 quoted here).
Dec 4 '06 #27

P: 22
Ah ok I was trying out a few things the date was never sorted originally I just left it in there as it didn't seem like it was affecting anything, but yes you did catch me taking it out!

The export is just a macro that the button runs, I did convert the macro to a module so I could get code if you want that but otherwise there is no code.

This comes up when I push the export button

Dec 5 '06 #28

NeoPa
Expert Mod 15k+
P: 31,660
Try taking out the space(s) from the file name.
I'm not too hot on macros myself. Always use code myself as there's no downside for me.
Let me know if the space thing fixes it.
If not, do as you suggest and convert it to code; test it; then let me know, as before, where it failed and the error message.
Dec 5 '06 #29

P: 22
Hello,

I converted the macro to VBA and put in the code inside the export button click sub and now when I click the export button it brings up this error.

The Microsoft Jet database engine could not find the object 'StudentCreation.txt'. Make sure the object exists and that you spell its name and the path name correctly.
Now I'm not sure why it looking for StudentCreation.txt as it's exporting it and should create the file, the path is also correct as the export all button creates the file fine with the same path.

The code for the export of just the newly added records is:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.ApplyFilter "ExportToday", "[DateAdded]=Date()"
  2.     ' Exports students into spreadsheet for uploading to tafevc
  3.     DoCmd.TransferText acExportDelim, "tab delimited", "ExportTodaysRecords", "D:\TafeVC Vista\Enrollment Database\KAL Enrollment Database\Test\StudentCreation.txt", False, ""
  4.     Beep
  5.     MsgBox "StudentCreation.txt has been exported please email to tech staff", vbOKOnly, "Student Export"
The error message doesn't bring up a debug button so I'm not sure if any code is malfunctioning.
Dec 5 '06 #30

NeoPa
Expert Mod 15k+
P: 31,660
See what happens if you create the new file in D:\.
(See earlier post about removing spaces from filename.)
Dec 5 '06 #31

P: 22
It still gives me the same error about the jet database engine even when it tries to save to d: I tried making a folder and getting it to save to that but that gave the same error again.

What would be the code to do the query inside the button click sub in vba rather than using a query in access?
Dec 6 '06 #32

NeoPa
Expert Mod 15k+
P: 31,660
Try :
Expand|Select|Wrap|Line Numbers
  1. Call ChDir("D:\TafeVC Vista\Enrollment Database\KAL Enrollment Database\Test")
  2. DoCmd.TransferText acExportDelim, "tab delimited", "ExportTodaysRecords", "D:StudentCreation.txt", False
and let me know if and where it fails.
Dec 6 '06 #33

P: 22
Ok I got that same jet database engine error message with that code. I ticked DateAdded box so it would be visible in the query and it exported fine with your code.

Can I sought it by the date then tell it to remove the date entry before export?
Dec 7 '06 #34

NeoPa
Expert Mod 15k+
P: 31,660
Ok I got that same jet database engine error message with that code. I ticked DateAdded box so it would be visible in the query and it exported fine with your code.

Can I sort it by the date then tell it to remove the date entry before export?
I would think not.
I've come across this behaviour when grabbing data from Access using Excel.
You could 'export' the data to a table first with a SELECT ... INTO or INSERT ... INTO query.
From there you can export what you wish.
There may be a way of 'fixing' what you're doing at the moment, but without direct access to all you've got it's difficult to say.
Dec 8 '06 #35

P: 22
Hello,

Its not a huge annoyance having the date in the text file I can just open it up in excel and delete the date column.

You can download the db here to have a look at it Database

Cheers
Dec 12 '06 #36

NeoPa
Expert Mod 15k+
P: 31,660
Hello,

Its not a huge annoyance having the date in the text file I can just open it up in excel and delete the date column.

You can download the db here to have a look at it Database

Cheers
As I always say, if only we had all the informatin we'd just be working blind and not with our hands tied behind our back too.
Ahrjay, your problem is that the Export spec 'tab delimited' expects the Date field to be there.
I took your copy and updated that and it worked fine.
Sorted and selected by Date (you really don't need both!) and happily produced the results excluding the Date field.
Microsofts error messages don't help in a situation like this.

Let me know if you have problems getting it to work.
Dec 12 '06 #37

P: 22
What exactly do I do with the export spec? I went through th manual export and removed the date field from the tab delimited export spec but it said the number of fields in my table doesn't match the number in the export spec? What did you do to fix the probelm?

Cheers
Dec 13 '06 #38

NeoPa
Expert Mod 15k+
P: 31,660
QueryDef ExportTodaysRecords:
Removed check to display DateAdded and saved.

Function StudentCreation()
Made Public
Public Function StudentCreation()
Removed line
DoCmd.ApplyFilter "ExportToday", "[DateAdded]=Date()"
Changed line to export QueryDef ExportTodaysRecords instead of table and deleted last "" parameter
DoCmd.TransferText acExportDelim, "tab delimited", "ExportTodaysRecords", "StudentCreation.txt", False
You can keep the filename as it was but my system doesn't have a D: drive.

In the Database again.
Run File / Export.
Select 'Save as Type' as Text File.
Click in order on Export; Advanced; Specs.
Open 'tab delimited'.
Select last (Date) line and deleted it.
Click on 'Save As...'
Overwrite 'tab delimited'.

Run the fixed code.
Dec 13 '06 #39

P: 22
I did exactly as you said and I still got the error of the number of fields not matching the table compared to export spec.

I unticked the DateAdded field in the ExportTodaysRecords query and kept the Date() criteria

I removed DoCmd.ApplyFilter "ExportToday", "[DateAdded]=Date()" from my button click code for the exporting

I changed the DoCmd to DoCmd.TransferText acExportDelim, "tab delimited", "ExportTodaysRecords", "D:\TafeVC Vista\Enrollment Database\KAL Enrollment Database\Test\StudentCreation.txt", False

Then went to resave the export spec and got the error mentioned above
Dec 13 '06 #40

NeoPa
Expert Mod 15k+
P: 31,660
Ahrjay,

Firstly sorry for the delay in responding, I was unable to get into TheScripts yesterday at all.
You don't say which steps you took to save the Export Spec (the bit that went wrong). So I can only guess that my not specifing that the object that need to be exported (Selected when you say File / Export...) is the query 'ExportTodaysRecords'. It sounds like you were trying to export the table again.
Dec 14 '06 #41

P: 22
No worries I couldn't get to the forum either, updating then the server kept dishing out sql errors ironically.

Yes you are right in your assumption that I was trying to export the table and not the query it now works perfectly, thanks for all your help your truly one of the most help moderators I've ever come across on a forum...ever.

Definately be hanging around the forum to see if I can help out people in stuff I know.

Cheers
Dec 14 '06 #42

NeoPa
Expert Mod 15k+
P: 31,660
That's not a problem Ahrjay - I was pleased to be of help.
And we like to encourage everyone to come back and help others so ups to you for that.
Finally a little secret.
I was only able to get the details right after you posted the database for me to see directly. Maybe you'll understand later (after you've been answering questions yourself for a while) how much easier it is to understand the problem when you can see it directly. Usually the requestor doesn't tell you the important bits - because if he understood they were important he generally wouldn't need to ask in the first place.
Dec 15 '06 #43

Post your reply

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