Full Version: Adding records using code?
UtterAccess Forums > Microsoft® Access > Access Forms
drk_dragoon
Hello everyone,
Am at a stopping point in my database (driving me nuts). Heres the situation. I have a table with this information:
ProgramID
PhaseID
CustomerID
ResourceID
QuarterID
Value
Oalso have two forms to enter this data into the table. The first form contains programID, PhaseID and CustomerID. Once the user selects all these, they click the next button which adds these values into global variables. Form number 2 is now opened.
Heres my problem. I need to manually add records to my table based upon which textbox the VALUE is entered in. For example, I enter in "16" into a textbox that has ResourceID of 1, and a quarter ID of 3. This value has to be entered into the table. When I enter a different value for textbox 2, lets say "15" this has to also be entered as a different record in my table. But keep in mind, this is all on 1 form and there will be TONS of textboxes. When the user enters in all the information, they will click add and *a new record will be added for each textbox on the form*.
How am I going to accomplish this task? I have been trying to use recordsets to manually code in for each textbox, but I am having troubles doing this.
Anyone have any ideas or do I need to clarify further? I cant post the DB because its confidential info inside.
Thanks alot for the help!
Girn13
On click of "AddNewRecord"
If nz([textbox1])<>"" then add_A_record txtRID1, txtQID1, txtVal1
if nz([textbox2])<>"" then add_A_record txtRID2, txtQID2, txtVal2
etc......
Private sub add_A_record(RID as string, QID as string, Val as string)
dim db as database
set db=cutrrentdb
db.Execute "INSERT INTO [Yourtable] ( ProgramId, PhaseID, CustomerID, ResourceID, QuarterID, Value)" & _
" Values(" & [Global1] & ", " & [Global2] & ", " & [Global3] & ", '" & [RID] & "', '" & [QID] & "', '" [Val] & "');"
set db=nothing
end Sub
***Note that for each value you must have its syntax correct. If ResourceID is a text filed then the RID must be wrapped in single quotes as keyed above. If ProgramID is a number then no sytax surrounds global1. If it were a date it would look like this " Values(#" & [Global1 & "#,........
drk_dragoon
Looks like thats what I need, but im having some problems with the code:
b.Execute "INSERT INTO [Yourtable] ( ProgramId, PhaseID, CustomerID, ResourceID, QuarterID, Value)" & _
" Values(" & [Global1] & ", " & [Global2] & ", " & [Global3] & ", '" & [RID] & "', '" & [QID] & "', '" [Val] & "');"
Those two lines are RED in my code and the error says "expected end of statement"
Im a little bit of a noob when it comes to this, but my textboxes are named txtBox1, txtBox2, etc...
So, if im reading this right, when the db.execute command runs, INSERT INTO [tblData] will take my existing fields and dump the three global variables and 3 textbox values into the record, and save. When I run this statement:
if nz([textbox1])<>"" then add_A_record txtRID1, txtQID1, txtVal1
VB will automatically take the value entered into txtRID1 and put that in the RID section of the add_a_record function (automatically turn it into a value).
Correct?
What does nz([textbox1]) mean?
Thanks for the response... if its not too much trouble maybe you can elaborate.
Fletch
There's a syntax error causing that line to turn red. Near the end, right before [Val] you need another & as in
db.Execute "INSERT INTO [Yourtable] ( ProgramId, PhaseID, CustomerID, ResourceID, QuarterID, Value)" & _
" Values(" & [Global1] & ", " & [Global2] & ", " & [Global3] & ", '" & [RID] & "', '" & [QID] & "', '" & [VAL] & "');"
That being said, I'm not sure that it will work as it stands. You probably need to change the variables some what, as what was given to you is generic. Also, I wouldn't use VAL as a variable since Val() is a function. If ProgramID is in Text1 and PhaseID is in Text2, etc., you might have
db.Execute "INSERT INTO [Yourtable] ( ProgramId, PhaseID, CustomerID, ResourceID, QuarterID, Value)" & _
" Values(" & Me.Text1.Value & ", " & Me.Text2.Value & ", " & Me.Text3.Value & ", '" & _
Me.Text4.Value & "', '" & Me.Text5.Value & "', '" & Me.Text6.Value & "');"
And not even use a function. Or, if you do, you may not have any global variables.
Does that make any sense?
Also, remember what Girn13 said about surrounding text fields in quotes but not number fields.
Also, Nz is a function that if null, returns an alternate value. If it is null, it just returns the value you send to the function.
So, Nz(textbox1]) will return the same result as [textbox1] unless [textbox1] is Null. Then, it will return either a 0 or an empty string (look it up in the help file for more info on that). Or, you can specify what to return if null as in Nz(somevalue, "It's null") will return the string "It's null" if somevalue is Null.
Does that make any sense? frown.gif
drk_dragoon
Thanks for the input! Cleared up alot of things for me.

My only problem now...

Dim db As database - USER DEFINED TYPE NOT DEFINED

Also, Program, Phase, and Customer are global variables carried over from the first form. Resource and Quarter need to be hard coded into the code, because of the form layout (excel type form). The only value im getting from the textbox is VALUE. The rest is based on where the textbox is on the grid.


Just to clear things up, every single field on here (Program, Resourse, Value, etc) are NUMBERS

Thanks again!
Edited by: drk_dragoon on Wed Sep 29 13:15:51 EDT 2004.
drk_dragoon
I was getting this "user defined type not defined" error when I was using recordsets too... why isnt it working? Im in access 2002, using VB editor...
drk_dragoon
Nevermind, I fixed that part using a basic search...
How I get: Runtime error 3134 SYNTAX ERROR IN INSERT INTO statement
db.Execute "INSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, ResourceID, QuarterID, Value)" & _
" Values(" & Me.txtBox1.Value & ", " & Me.txtBox2.Value & ", " & Me.txtBox3.Value & ", '" & _
Me.txtBox4.Value & "', '" & Me.txtBox5.Value & "', '" & Me.txtBox6.Value & "');"
RoyVidar
You say all your fields are numeric, stilll you're using text delimiters (single quotes) on some of them. Also Value might be a reserved word, I've enclosed it in (parens)
CODE
db.Execute "INSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, ResourceID, QuarterID, [Value])" & _
" Values (" & Me.txtBox1.Value & ", " & Me.txtBox2.Value & ", " & Me.txtBox3.Value & ", " & _
Me.txtBox4.Value & ", " & Me.txtBox5.Value & ", " & Me.txtBox6.Value & ");"
Girn13
I'm reading that CutomerID, ProgramID and PhaseID are numerics and that ResourceID, QuarterID and Value are text. If that is not true you'll have to adjust the single quotes you have around the variables. If that doesn't do it I'll clutch at this straw.... I shortened "value" to "val" so I wouldn't have contention, forgetting about the function, good catch by babrandt. Copy tbldata to a new name, change the field named "Value" to "Valewe", change the query too, then try it.
drk_dragoon
ALL of the fields are numerics. CustomerID, ProgramID, PhaseID are global variables. ResourseID and QuarterID are going to be hard coded in (hard to explain, but it will be hard coded depending on textbox position), and Value will be the field out of ONE of the textboxes on the form.
also have an ID field... but shouldnt that update automatically when I add this new record (its autonumber of course)?
For some reason its still giving me the same error... What am I doing wrong here???
Private Sub btnAddRecord_Click()
If Nz([txtBox1]) <> "" Then add_A_record "1", "2", txtBox3.Value
If Nz([txtBox2]) <> "" Then add_A_record "3", "6", txtBox4.Value
End Sub
Private Sub add_A_record(RID As Integer, QID As Integer, ValID As Integer)
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "INSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, ResourceID, QuarterID, DataValue)" & _
" Values(" & intCustomerID & ", " & intProgramID & ", " & intPhaseID & ", " & _
RID & ", " & QID & ", " & valID & ");"
Set db = Nothing
End Sub
Girn13
You're sending string data "1", "2" to an integer. change it to
If Nz([txtBox1]) <> "" Then add_A_record 1, 2, txtBox3.Value
drk_dragoon
Grrr now when I click the command button it does NOTHING. I checked the data table, but no information was entered. I also checked the ON CLICK event of the button, which does correctly point to EVENT PROCEDURE. Heres my code... once again (thanks for being so patient guys):
Private Sub btnAddRecord_Click()
If Nz([txtBox1]) <> "" Then add_A_record 1, 2, txtBox1.Value
End Sub
Private Sub add_A_record(RID As Integer, QID As Integer, ValID As Integer)
Dim db As DAO.Database
Set db = CurrentDb
intCustomerID = 1
intProgramID = 2
intPhaseID = 2
db.Execute "INSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, ResourceID, QuarterID, DataValue)" & _
" Values(" & intCustomerID & ", " & intProgramID & ", " & intPhaseID & ", " & _
RID & ", " & QID & ", " & ValID & ");"
Set db = Nothing
End Sub
If this was working correctly, I should get an entry in my tblData that has:
CustomerID - 1
ProgramID - 2
PhaseID - 2
ResourceID - 1
QuarterID - 2
Value- (whatever number I enter into txtBox1)
ID-1
Am I correct?
RoyVidar
Debugging - place breakpoints on relevant lines (F9), try running and step thru each line (F8). Hover the mouse over the variables to see what values they contaion. Also do a debug.print of your sql string, then hit ctrl+g to study it. You should be able to either pinpoint the challenge, or copy it, and paste it into the sql view of the query builder and run it (or post it here) .
Is it a Null test you're doing? Try some other variants, for instance if trim$(me!txtBox1.value & "") = "" then, or some other...
drk_dragoon
I did the debugging step, all of the variables at breakpoint Set db = Nothing are CORRECT when I put my mouse over them. For some reason its just not writing them to the table... and I have no clue how to correct that problem :(
RoyVidar
Haven't the foggiest, I'm afraid.
That happens if you run it thru the runsql method of the docmd object, and use literals all the way?
docmd.runsql "NSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, ResourceID, QuarterID, DataValue)" & _
" Values(1,2,2,1,2,1);"
What messages pops up?
drk_dragoon
Well, I tried that and it said it was going to append record. When I said yes, it told me access set 0 records because of a type conversion failure, 0 records to key violations, 0 records to lock violations, 0 records to validation records. It didnt successfully write the data, but it did do something at least.
ELP! Your code looks so close!
Fletch
We can keep going back and forth, but if you can post your database (preferably in A2K format) and tell us which form and which button you're dealing with, we can probably clear this up quickly.
The type conversion failure, however, indicates to me that the data type in your table does not match the data type you're sending it with the insert into statement--therefore, if you can't post your database, look at the table design for that table and tell us the exact data type of each field.
Also, as an aside, I would recommend switching
If Nz([txtBox1]) <> "" Then add_A_record 1, 2, txtBox1.Value
to
If Nz([txtBox1], "") <> "" Then add_A_record 1, 2, txtBox1.Value
So you can guarantee the return result of Nz (if not specified, will either be "" or 0, depending on the data type). frown.gif
RoyVidar
Beat me to it wink.gif
drk_dragoon
Ok, I have attached a stripped down copy of my database, which should have all the nessisary items on frmDataEntry2 for testing purposes. FYI frmDataEntry1 is only a layout to show you how i will get the other three variables... Its not functional.
The tblData all has number fields, so there should be no type mismatch...
When I click the command button, it does nothing. Hopefully someone will see a simple solution...
Ignore some of my notes... just bits of code im trying out!
Thanks again guys, I tried working on this for another hour or so but still no luck...
drk_dragoon
As an important note, I had to change the extention of the database to a .jpg because UA wont let me upload mdb files???? WTF am I doing wrong now?
All you have to do is open the file using Access, works fine.
Girn13
If everything executes to your satisfaction meaning the code you expected to execute, executed with the values in the variables that you expected to see then...
Does the table you're attempting to write to have other fields then the ones we've been working with? if the answer is yes then look at how those fields are defined. If you have "AllowZeroLength" set to no and attempt to insert a record with nulls in that field it will not work even though the code executes successfully.
drk_dragoon
What you see in the file is exactly what im working with. The only thing that isnt listed is the autonumber ID field...
The code executes, nothing is entered into the underlying table.
Fletch
First of all, you can't upload mdb files b/c they generally waste lots of space. Please zip mdb files before uploading to minimize size (zip files are accepted by UA).
That being said, the database works fine on my machine. The only exception was that the DAO reference was not checked which resulted in a compile error where you dim'ed db as type Database. So, verify that you have that reference checked. As an alternative, you don't really need that variable. Try
CODE
Private Sub add_A_record(RID As Integer, QID As Integer, ValID As Integer)
intCustomerID = 1
intProgramID = 2
intPhaseID = 2
CurrentDb.Execute "INSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, " & _
                  "ResourceID, QuarterID, DataValue) Values (" & _
                  intCustomerID & ", " & intProgramID & ", " & intPhaseID & ", " & _
                  RID & ", " & QID & ", " & ValID & ");"
End Sub

Then, you shouldn't need the DAO reference, at least for that sub. If you do need it and don't know what reference I'm referring to, then in the VBA editor, click Tools->References. Make sure there is a check mark next to "Microsoft DAO 3.6 Object Library."
HAs a final design note, your fields in the table are all of long data type. However, this function only takes integers as arguments. So, I would either redefine the fields or the arguments (and global variables) so that you are consistent. Right now entering a number > 32767 or < -32768 will create an overflow error.
HTH frown.gif
Girn13
Now for the real bummer. I just downloaded your db and it ran just fine for me!!
drk_dragoon
Lol, just great...
ell... the darned thing doesnt work for me. I click the command button and nothing happens... just like if you would put a button on a form with no code behind it... :(
Odouble checked the variables after the code runs. EVERYTHING IS FINE... why the [censored] isnt this button working...
drk_dragoon
I revised the code to a combination of things that have been posted:

CODE
Private Sub btnAddRecord_Click()
If Nz([txtBox1]) <> "" Then add_A_record 1, 2, txtBox1.Value
End Sub
Private Sub add_A_record(RID As Integer, QID As Integer, ValID As Integer)
intCustomerID = 1
intProgramID = 2
intPhaseID = 2
DoCmd.RunSQL "INSERT INTO [tblData] (CustomerID, ProgramID, PhaseID, ResourceID, QuarterID, DataValue)" & _
" Values(" & intCustomerID & ", " & intProgramID & ", " & intPhaseID & ", " & _
RID & ", " & QID & ", " & ValID & ");"
End Sub


This actually prompts me to append records, so im finally past that hurdle. It works.

However... im getting an annoying error and I dont see why. All of my data types are NUMBER and thats all im entering into this statement...but access is still complaining about something.

Take a look for yourself, i attached the error into a picture.

Thanks alot for everyones patience and help. Im really trying to fix these errors by myself, but when I sit for more than 30 minutes with no solution, its probably not going to come to me ever...
Edited by: drk_dragoon on Thu Sep 30 10:12:32 EDT 2004.
Fletch
Sometimes error messages can be misleading, but sometimes they're helpful--at any rate, I can understand your frustration! frown.gif
id you strip anything out of your database before posting? I'm showing a single table named tblData and two forms, frmDataEntry1 and frmDataEntry2 and nothing else.
Also, what value are you typing into the txtBox1? It's a long shot, but I want to double check that.
The key violation error you're getting is interesting--that implies you're doing something such as trying to insert the same value into the table in the same field where there is a unique index. However, I checked your indices and the PK, an autonumber field, appears to be the only one.
A thought just occurred to me--you're linking CustomerID to a tblCustomers, but I have no such table. That alone could be the source of your error if there's a relationship there where you enforce referential integrity and the customer doesn't exist in tblCustomers! You also reference tblResources and tblPhases which I don't have.
Just for giggles, I'll attach the database that I have of yours that works on my machine. Give it whirl and see what gives. frown.gif
Girn13
All righty then. THE CODE WORKS. And furthermore the whole thing works on my machine so....
Here are the exact references, AND THE ORDER they are in on my copy. I've had a couple of occasions where by moving the references around I've had things work instaed of fail.
VB for Applications
Access 10.0 Object lib
ActiveX data Objects 2.1 Lib
Ole Automation
DAO 3.6 Object Lib
Another possibility(this one's a reach.). Do you have any relationships that you stripped out in order to send the test db? maybe the error is occuring there.
drk_dragoon
Ahhh! You were right.

tblCustomers
tblPrograms
tblPhases
tblResourse
tblQuarters

All these have an ID field that is related to the data in tblData. However, there were no records in tblPrograms... the source of my problem all along. RELATIONSHIPS were still turned on, which was screwing up my data entry.

Many thanks to everyone that contributed to helping me with this problem! In the end it was a stupid mistake, but... *embarassed*

Oh wait, one last thing. Is there any way to code into the program to 'fake' access into thinking the user clicked OK to the "append 1 record..." prompt box? I know in a batch file you can do a /y to automatically enter y for yes without the user even being prompted.

Any ideas? *UPDATE* PROBLEM SOLVED. I reverted back to the old CurrentDB.Execute statement and I am not prompted anymore, it does it automatically.

THANKS ALOT EVERYONE! Hopefully this topic will help someone in the future. frown.gif frown.gif

Edited by: drk_dragoon on Thu Sep 30 10:59:38 EDT 2004.
Edited by: drk_dragoon on Thu Sep 30 11:02:19 EDT 2004.
Girn13
if you want to turn it off permanently...
Tools > Options > Edit/Find and uncheck everything in "confirm"
o do it on a case by case basis
Whereever you want it done
set warnings off
Your code

set warnings on
Fletch
You're welcome and glad to have been a small part in the solution! frown.gif I only wish I had thought of this possibility sooner! frown.gif Anyway, good luck with the rest of your db! laugh.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.