Inserting Values

Nov 16, 2006

HI,
I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow.
my form made of a combination of two tables attributes ad when I enter say:

name
tel number
address
car
car reg
booking time

then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking.
thanks

View Replies


ADVERTISEMENT

Inserting Values Into Multiple Tables

Aug 22, 2005

I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!

I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials.
I have three Tables: tblX, tblY, tblZ.
tblX stores details for each individual material. Its structure is as follows:

tblX
XPK | ID | value1 | value2 |
01 | A | asdfas | asdfdf
02 | B | dfasdfa | sdfaf
03 | C | asdfffd | asdfg
(here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)

tblY stores materials that are combinations of two primary materials:
tblY
YPK | 1st | 2nd | ID | value1 |
101 | 01 | 03 | M | keiury |
102 | 02 | 03 | N | kjgeiih |
103 | 03 | 01 | P | djlkgoi |

(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)

tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:

tblZ
ZPK | 1st | 2nd | 3rd | 4th | ID | value1 | value2 |
1001 | 01 | 03 | -- | -- | R | asdprw | mnvd |
1002 | 01 | 102 | 02 | -- | S | adsfd | oirtyr |
1003 | 103 | 02 | -- | -- | T | werwq | pojfgr |
1004 | 02 | -- | -- | -- | U | alkfdp | uioite |
1005 | 01 | 02 | 103 | 102 | V | keqwei | oirewj |

(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)

So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...

I hope I am clear enough till this point.

My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:

1) save each primary material from tbltemp to tblX
2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.

I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.

I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).

If you are still reading, I already owe you a ton of thanks!!

View 7 Replies View Related

Modules & VBA :: Inserting Values Into Table With SQL

Sep 12, 2014

I'm trying to insert a set of values into a table using SQL.

Here's my code:

DoCmd.RunSQL "INSERT INTO tblAuditTrail([DateTime], [UserName], [RecordID], [Action], [FieldName], [OldValue], [NewValue])VALUES (" & Now() & ", " & User & ", & Me.CSM & ", " & 'EDIT' & ", " & 'Location' & ", " & Me.txtTranFrom & ", " & Me.txtTranTo & ");"

However, this returns a compile error.

I reference User as Environ("USERNAME") earlier in the code. Everything else pulls from the current table ("tblInventory").

I'm lost, since I've never attempted to use SQL in VBA.

View 3 Replies View Related

General :: Inserting Values From Combobox In Table

Mar 6, 2013

I am a newbie to ACCESS. I am stuck with inserting values of combobox directly into a record in table.

I have five different tables(each has only one cloumn) and a complete full_details table(contains 5 cloumns).

I have a simple form with five comoboxes and a save button on it. The five comboboxes are populated from five different comboxes at the time of form load. Now, I want to add values selected from these comboxes in fulll_details table.

My form is unbound. I tried with VBA code, on the save button click, I have written ,

Private Sub save_Click()
CurrentDb.Execute "Insert into Full_Details(Col1,col2,col3,col4,col5) Values ('" & Combo0 & "','" & Combo1 & "','" & Combo2 & "','" & Combo3 & "','" & Combo4 & "');"
MsgBox "Record Saved !!!", vbInformation, "Success"
End Sub

It gives me some error : "You can't reference a property or method for a control unless the control has the focus property set"....

See, above query works fine , if I set focus of one combobox,but it adds only 1 combobox value in full_details table as only 1 column value.

View 6 Replies View Related

Queries :: Inserting Values From A Listbox Into A Table

Jun 28, 2013

inserting values from a listbox in to a table. I have a table with a field named: PrefReports. This field will hold data from values in a list box that the user selects.

Table: Profiles
Field Name: PrefReports Type: Memo

The user utilizes a form to input the values necessary for the query. I then have the query tied to a command button. When the command button is clicked, the query is executed.

The query is as follows:

INSERT INTO TblProfiles ( [Employee ID], FName, LName, Barcode, PrefReports )
VALUES ([forms]![FrmCreateNewProfile]![txtEmployeeID], [forms]![FrmCreateNewProfile]![txtEmployeeFName], [forms]![FrmCreateNewProfile]![txtEmployeeLName], [forms]![FrmCreateNewProfile]![txtBarcode], [forms]![FrmCreateNewProfile]!lstReports.Value);

How can I take what the user selects in the list box, and insert into my TblProfiles.PrefReports field? When I run the query, all other information makes it to the table except the list box values.

View 3 Replies View Related

Queries :: If Function And Inserting Null Values In False Part

Oct 7, 2013

I have a calculated field (Bhours) with the following IIf function:

=IIf(Fields!BDate.Value<=Parameters!Pdate.Value and Fields!EDate.Value>Parameters!Pdate.Value,Fields!H ours.Value,0)

It works perfectly, but I don't like the fact that it has a zero for the false part as I like the field to the blanc instead of filled with a zero.I can't use a space as I need to calculate with this field later on. I tried using "", but then the field displays #error".

View 7 Replies View Related

Forms :: Method Or Data Member Not Found When Inserting Values To A Table

May 13, 2013

I am getting method of data member not found for the following code when inserting values to a table. Form has three frames. Add/Insert is on OnClick Event.

MsgBox "First Value, " & Me.txt_FSNo
CurrentDb.Execute "INSERT INTO BackchargeLog (BC_FSNo, BC_FContract, BC_FPurchaseOrder, BC_FProjectArea, " & _
" BC_FTitle, BC_FDetailDescription, BC_FSupplierToBeCharged, BC_FSupToBeChargedNumber, BC_FSTBCContactName, BC_FSTBCContactNumber, " & _
" BC_FSTBCContactEmail, BC_FIncidentReportedDate, BC_FInitiatedDate, BC_FEstimateAvailable,

[Code] .....

View 7 Replies View Related

Modules & VBA :: Inserting From Excel Number Of Query Values And Destination Fields Not Same

Jan 3, 2014

I am just beginning to manipulate Access from Excel.

I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.

I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.

When I try to use the second version I receive the error message...

Number of Query Values and Destination Fields are not the same.

The string that works perfectly is:-

Code:

strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"

The one that generates the error message, which is identical except that I have removed the list of fields is:-

Code:

strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"

The execute command is:-

Code:

gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords

View 1 Replies View Related

Error At Inserting New Value

Jan 24, 2006

i got the atached message when i am trying to insert a new value, the wierd thing is when i do okayu, the value is being insrted...

View 5 Replies View Related

Need Help Inserting Table

Jul 31, 2006

I have a database that contains a few tables and queries etc. I made a copy of this database so that I could change some of the VB code with out affecting the database information. I have completed all the code and now I want to input those old tables into my finished verion. I am not sure how to do this. Any ideas on how I could do this would greatly be appreciated. Thanks.

View 8 Replies View Related

Inserting A Video

Feb 26, 2007

Hi
I really need to insert a movie in my database. I have tried inserting a Windows Media Player object in a form, but I dont know how to link it to a movie on my computer. Please help me with this. By the way I'm using DAO, if that helps at all.

Please help me as soon as possible, I'm in a hurry
Thanks in advance

View 4 Replies View Related

Inserting Columns

Dec 30, 2004

i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas?
thanks
briar

View 6 Replies View Related

Inserting Records...

Aug 4, 2005

Ok, I know this is already part of another thread, but my problem is a bit more complex...

The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.

I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.

Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.

That's my solution, but there may be something simpler. Any thoughts?

View 7 Replies View Related

Inserting Into A Table

Apr 23, 2007

just wondering if anyone knows how to insert about 10 lines of data into an already existing table?

View 3 Replies View Related

Textbox Value Not Inserting

Jan 23, 2007

Hi,

Can anyone tell me what i'm doing wrong with this insert statement?


INSERT INTO TBL_PROCESS_NAME
VALUES( ' & [Forms]![frmIntroduction]![txtProcess_Name] & ' )


the form is loaded when the query is run, i've tried taking out the & and the ' but to no avail, all i get in the table is [Forms]![frmIntroduction]![txtProcess_Name]

Thanks

Michael

View 7 Replies View Related

Inserting Into A Table With 2 Pks

Apr 5, 2007

The security table is made up of two primary keys: thing, personorgroup
When i run this statement to insert into the security table

INSERT INTO security (thing, personorgroup, accessrights)
SELECT '252600649', '4020', '255'
FROM PROFILE
WHERE not exists(select * from security
where security.thing = '252600649' and security.personorgroup = '4020');

I get this error:
Server: Msg 2627, Level 14, State 1, Line 25
Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'.
The statement has been terminated.

Anybody know how i can perform my insert successfully? :D

View 9 Replies View Related

Inserting Value In Table

Sep 23, 2004

i am trying to achieve a simple thing (at least it seems so to me!)...
I am trying to add a new record to a table (LP_Product_Name) with only one field (Product_Name) as a part of a 'on Lost Focus' event of a text box (txb_ProductName).
Code:Private Sub txb_productname_LostFocus()' Declare Variables Dim db As Database Dim rs As DAO.Recordset Set db = Nothing Set rs = Nothing' Assign Values to Variables Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset("LP_Product_Name")' Enter New Product name to the table With rs .AddNew .Fields("Product_Name") = Me.txb_productname End With ' Close variables Set db = Nothing Set rs = Nothing' Restore Visible formMe.cmb_productname.Visible = TrueMe.cmb_productname.SetFocusMe.txb_productname.Visible = FalseEnd Sub
When I type something in the textbox and shift focus to another field, nothing happens... no error message and no added value to the table! I dont know what is going on. It just doesnt work!

On the same form I also have a combo box that uses the same table (LP_Product_Name) as its rowsource. I want the Combo box to immediately show this added value.

Can someone please help!?!
Thanks!

View 7 Replies View Related

Help With Inserting Date

Jan 8, 2007

Hi All,
This should be a simple thing however Im having some problems. Im trying to insert a record into access. I can insert everything except the date. I cant seem to get the right format. Below is what Im trying:
Code:strSQL ="INSERT INTO GISrequest (givenName, surName, email, division, title, purpose, audience, date) VALUES ('firstmname', "&strVal1&", 'anemail@somewhere.com', 'thedivision', 'thetitle', 'purposestring', 'audience',#" & AccessDate(NOW()) & "#)"

I think the error is in how I format the date, but Im not sure. Ive looked on the net for examples but I havent found anything that works... can someone suggest what may be wrong or perhaps provide (or point me to ) an example of inserting a date into an access database with ASP?

Many Thanks

View 3 Replies View Related

Inserting Error???

Jan 10, 2007

hEYA all, ive got a problem, im trying to inserting data from an array to my access table- but cant do this due to this error:-Data type mismatch in criteria expression.

Heres my insert code- which its genereating the error at:
Code:sub InsertDBCmd( )'================================================= ========================================SET objRs = SERVER.createObject( "ADODB.recordSet" )dim sqlStatmentsqlStatment = "INSERT INTO Members "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "[A/C no], [Address_Code], "sqlStatment = sqlStatment & "[Managing Director], [Title], [Buying Group Member], [M D Sal], [Rebate Contact], " sqlStatment = sqlStatment & "[Rebate Title], [Rebate Sal], [Replied], [Member Name], [S/Holder 2], [S/Holder], " sqlStatment = sqlStatment & "[ALPHA TYPE], [Member Type], sqlStatment = sqlStatment & ") "sqlStatment = sqlStatment & "VALUES "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "'" & C & "', '" & No & "', " For n = 1 to 15 if n <> 15 then sqlStatment = sqlStatment & "'" & FieldsArray(n) & "', " else sqlStatment = sqlStatment & "'" & FieldsArray(n) & "' " end if Next sqlStatment = sqlStatment & ") "'rESPONSE.WRITE(sqlStatment)'RESPONSE.ENDobjRs.OPEN sqlStatment, objConn,1,3'================================================= ========================================end sub

Any code or suggestions pls??

View 1 Replies View Related

Comparing 2 Tbles And Then Inserting New Only

Sep 17, 2005

I have two tables named datadistint and links
and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.

Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat)
Select 1,3,2,4,5 From datadistint Where
1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links)
and 2 Not in (Select LinkDescription From links)
and 4 Not in (Select maincat From links)
and 5 Not in (Select cat From links)

i ran the querry as above but it doesn't work what should i do or what's wrong with this querry,
because i need to compare all five fields and then insert only new values.

View 1 Replies View Related

Inserting The Records Into The Table

Oct 31, 2006

Hi!
Please, please, please help me!
I have created a form that should get Date, Day inputed by a user. Then it must place 8 the same records in the table.
Any help will be appreciated
Thanks in advance

View 2 Replies View Related

Inserting A New Field With A Fixed Value

Oct 19, 2004

How do I add a new field to an existing table so that the same value is automatically entered for all the records in that new field.

View 1 Replies View Related

Inserting An Object In A Table

Jul 28, 2005

I have been inserting thumbnail pictures into my table for the last six months with no problem. The field in my table displays "Microsoft Photo Editor 3.0 Photo." Now when I enter my thumbnail by inserting an object, the field displays the word "package" and the photo does not appear in the form where the pictures are displayed.
What would cause the word "package" to appear when I am inserting an object just like I have been doing for months?

View 2 Replies View Related

Inserting A New Row Between Existing Rows

May 15, 2007

To Whom It May Concern

I would like to know how can I insert a new row between an existing row in an Access Table

For example,

I want to insert a new row between line number 122 and 123. Do I need to get rid of the primary key or change it to Number. What I am doing now in order to have several topics group together on a report such as "Activities Topic" and a Date field for that topic. However, there are various topics under the Activities topic. Each new information that is being updated for this topic I have to reorder the number by the topic name in the Order field so that the date will be in chronoligal order in the report.

Is there a better way in do this?:confused:

View 1 Replies View Related

Missing Records When Inserting

Feb 5, 2008

Hi!

I hope this is the right section for posting this Q.

I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).

Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:


strSql = "INSERT INTO tblDetailNakup " & _
"SELECT tblDetailNakupTemp.* " & _
"FROM tblDetailNakupTemp;"
docmd.runsql strSql


most of the time it works OK, but from time to time a couple of records are missing.

What could be the problem? Is there any way for somekind of a check, if all has been inserted otherwise the query is repeated?

TNX in advance,
Miha

View 2 Replies View Related

Help Needed Inserting Formula

Mar 13, 2006

Hi,

I am new to microsoft access. I am trying to develop a data base that will store data as well as return means and SEMs. I have multiple parameters entered into an access table. The fields are Group Number, MAP, AP, and KV and are stored in a table called "Source Table". I have a query to return the averages for each group, but I am having trouble making a query to return the SEM. the formula for SEM is

SEM = Standard Deviation / (Count ^ (1/2))

This should be easy because access will return Stdev and Count, but when I try and enter the formula into expression builder, all hell breaks loose. Any advice would be greatly appreciated.

Cheers,
DH

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved