On the creation of a reconrd in the main table I'm trying to auto fill a sub table with standard information for the user. This is what I'm using to insert into the sub table:
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
How to copy a column from one table and insert it into another table in the same database
Hi, All,
I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.
To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.
Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.
I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.
Newbie question: I have a database where we track dispatched with two tables, one called Slots, the other called Dispatches. In the slots table there are 2 fields, one called Week (which hold the Monday date for each week in the year), the other called Available (in this field we want to manually enter a number that will tell the system how many dispatches we can do that week). We want to automatically take the number from the Slots.Available and enter X number of blank records in the Dispatch table with the monday date. How can this be done? Then we will create a form that can be filled in with the balance of the information. Thanks
I have an access database that contain 2 tables and 1 form :
table1 named customers (custid-custname) table2 named sales(salesid-custid) form named form1 contain (salesid-custid) *note:in form1 custid is combobox
What i need is when i select an customer from combobox the column custid in sales table get custid not custname
I use this sql statement to get combobox items from customers table
Hello, I have a table with 2 fields - WeekID(autonumber) and WDATE(date/time). What I want to be able to do is to enter a value for the first date and then have the next 25 entries automatically entered. Each entry is to be 1 week greater than the previous entry. Ultimately giving me a list of dates, numbered 1-26, incremented weekly. I assume the DATEADD function is to be used but i cannot determine how. Any help / pointers is greatly appreciated. Thanks
Hi I am creating an employee db, with fields including employeeID, surname, firstname, partnerSurname, partnerFirstname, partnerID. As some partners work at the company, I want the partnerID to list (thorugh a value box) employeeID's that match surname to a partnerSurname and firstname to partnerFirstname. Is this possible? Any help greatly appreciated. Thanks in advance.
Insert into tableA (x,y,z) = (SELECT x,y,z FROM tableB) WHERE NOT EXISTS (select x,y FROM tableA,tableB WHERE tableA.x = tableB.x AND tableA.y = tableB.y)
Basically I want to insert data in tableA from tableB if it does not exist in tableA yet.
hi, i want to convert some below mentioned format into msaccess tables.
the exact scenario is that i have some text files from old applicaiton (pascal based) which is no longer working due to hardware compatibility problem...
which has generated some text files under below mentioned format...
I have a table which contains the following fields: WORKER,SUB SYS,JAN-08,FEB-08,MAR-08 I want to insert into this table each month the data from the second table for that month can someone tell me how to do this...each time I try the secnd table data TOTAL strarts a new row below the ones that are already there...hard to explain. I tried this and it didn't work:
INSERT INTO [ORACLE SORA ALERTS BY WORKER] ( [JAN-08] ) SELECT A.TOTAL FROM A INNER JOIN [ORACLE SORA ALERTS BY WORKER] ON (A.SYS = [ORACLE SORA ALERTS BY WORKER].[SUB SYS]) AND (A.WORKER = [ORACLE SORA ALERTS BY WORKER].WORKER);
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 0000A ENF 0000A FIN 0000A RAP 000AT CRT 000AT ENF 000AT FIN 000AT RAP 000AX CRT 000AX ENF 000AX FIN 000AX RAP I have a second table which contains the following fields: WORKER,SYS,TOTAL
WORKER SYS TOTAL 0000A CRT 1 0000A FIN 21 000AX CRT 1 000AX ENF 2 000AX FIN 11
What I want is to move the numbers in the TOTAL COLUMN INTO THE MONTH/YEAR COLUMS IN THE FIRST TABLE. Would look something like this:
WORKER SUB SYS JAN-08 FEB-08 MAR-08 0000A CRT 1 0000A ENF 0000A FIN 21 0000A RAP 000AX CRT 1 000AX ENF 2 000AX FIN 11 000AX RAP
When I run this query I want the data inserted into an existing table in the correct place. It is inserting the data in the correct column but not the correct rows. Please see attached screenshot. Can you please show me what I'm doing wrong.
INSERT INTO UNDISTRIBUTED ( Jun2003 ) SELECT Sum(CCur(a.AMT_TO_DISTRIBUTE)) AS AMOUNT FROM NOLDBA_RECEIPT AS a, NOLDBA_INT_CASE_STATUS AS b WHERE (((Trim([a].[id_case])) Is Not Null) AND ((a.ID_CASE)=[b].[case_id]) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False)) OR (((Trim([a].[id_case])) Is Null) AND ((a.CD_STATUS_RECEIPT)='H') AND ((a.DT_END_VALIDITY)=#12/31/9999#) AND ((Exists (select 1 from NOLDBA_RECEIPT k where a.dt_batch=k.dt_batch and a.no_batch=k.no_batch and a.cd_source_batch=k.cd_source_batch and a.seq_receipt=k.seq_receipt and k.ind_backout ='Y' and k.dt_end_validity =#12/31/9999#))=False) AND ((b.CASE_ID)=(select min (y.case_id) from NOLDBA_INT_CASE_STATUS Y, NOLDBA_INT_CASE_MEMBER Z WHERE A.ID_PAYOR=Z.MEMBER_ID AND Z.RELATION_CODE IN ('A','P') AND Z.CASE_ID=Y.CASE_ID))) GROUP BY b.IV_D_DO_CODE ORDER BY b.IV_D_DO_CODE;
I have a form that contains a textbox and a listbox. The listboxs rowsource is tblSPNZ. The table has 2 fields 1 is and autonumber and the other is where I want the results to go.
How do I make a command button populate the table? Avoiding putting any results into the autonumber field?
HI, Is it possible to save data in a table, just doing a check on the value of a combobox?
I need to insert a Num_CC value that I got from a table called TB_ANAGRAIFCA, in the table called TB_ALLEGATI. But the value of Num_CC depends on the value selected in a combo box. So I have to save the Num_CC related to the Provider selected in the combobox namade Nome_Provider.
Any suggestion?
I tried to draw in the form interested a textbox invisible, but I don't know how to retrieve the data. Please, HElp me ....
In my VBA code, i am trying to move some table records from table1 to table2. Both table1 and table2 has exact structures.
In the INSERT statement for table2, what is the easy way to insert a record from table1? I am trying to avoid having to specify each field name in the INSERT statement. Because, i have 100 fields in table.
Is there a way to INSERT a particular record from table1 to table2 without having to specify field names in the INSERT statement like in below example?
I currently have 10 tables in my database (All with the same colunm names and formats) however i want to click a button that will put all the tables into TEST_DOC, instead of doing it one by one. To do this this i would like some code either in SQL or VBA that will do this.
This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.
I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.
Eg.
Listbox: ID 1 2 3
User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.
Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.
The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.
Somehow up to a few weeks ago those two columns were updated.
Tracing the code and using the immediate window the user_name variable stores the login name corectly.
Hi I have a form that displays line items and their cost. At the bottom of the form I have a text field called text17 that uses a sum to total the value of all the line item costs with this : =Sum([Line Cost])
How would I pass that value into a database table?
