Write/update Multiple Lines Belongs To A PO?

Apr 18, 2008

Hello all,

How are you? Hope everything is well!

I have a question. If I have a form, which has underlying source is the table LineItem. The table has these fields PO#, Line Item, Cancel check box, and Cancel Reason list box. Each PO can have 1 to multiple line items. Once in a while, the users need to cancel certain line items and type a reason in the Cancel Reason box. When the users click the Cancel check box, it force the user to enter a reason.

However, in cases, some POs have 10-100 line items, and the reason are the same for all line items, and it is time-consuming and tedious to do one by one. I would like to find a way to enter reason in line 1, for example, and copy/update the rest of line items in that same PO with that.

Please advise HOW and WHAT COMMAND should I use to update the text in one field of one record (line item) to ALL other records (line items) in the same PO.

Please help! Thanks so much.

View Replies

Forms :: How To Break Update Statement Into Multiple Lines

Mar 7, 2014

I am unable to break the UPDATE statement into multiple lines and now I am getting "Syntax Error in UPDATE statement"

Following is the multiple line Update Statement

Dim strsql As String
strsql = "Update tblcurr" & _
"SET tblCurr.Currencyname =" & _
"[forms]![updatecurrency]![txtcurname]" & _
"WHERE (((tblCurr.Currencycode)=" & _
"[forms]![updatecurrency]![txtcurcode]));"
DoCmd.RunSQL strsql

Following is the one liner Update statement which, works perfectly

strsql = "UPDATE tblCurr SET tblCurr.Currencyname = [forms]![updatecurrency]![txtcurname] WHERE (((tblCurr.Currencycode)=[forms]![updatecurrency]![txtcurcode]));

View 4 Replies View Related

Can I Write Code To Update The Tables On The Be?

Aug 2, 2007

hi,

On the OnLoad event of a form, is it possible for the program to traverse or go through every single record (maybe with a while loop)?? If so, please send me the exact syntax.

Also, I want to update the tables on the back end. Is it possible to this in the be file? Where specifically would I have to put the code?

thanks

View 7 Replies View Related

Update (write) Current Record

Jun 27, 2006

I have a small problem: I just want my form to update the current record because I'm opening (with a button) a second form which uses the values of some of its table fields.
If I open 2nd form without updating, it will use old values, and if the record I was worknig with is a NEW record, 2nd form simply can't find it because it's not been written!
So:
1. I want to tell the button to UPDATE record before opening the 2nd form.
2. In addition, I could want to go to 2nd form DIRECTLY from the same field I want to update and use in 2nd form (through a Keypress event instead of a button), but this is more difficuolt because I still didn't EXIT from the field but I already want to use the text manually updated in the field... any idea?

Thanks

View 5 Replies View Related

Multiple Lines In Text Box

Dec 14, 2004

i'm no noob, but i'm also not the expert, what i'm trying to do is this,
i'm taking multiple string out of an recordset and putting them in one big text box
but the problem is that after every record there has to be an enter in the text box
So for example:

record 1 =
-bla bla bla
-tja tja tja

record 2 =
-waa waa

my result in the text box must be:
-bla bla bla
-tja tja tja
-waa waa

and not (like it is now):
-bla bla bla
-tja tja tja -waa waa

who has a clue? chr(11) & chr(13) do not work, i activated "on enter go to next line"

thx in advance for helping out

View 2 Replies View Related

Importing Multiple Lines Of Text

Aug 27, 2007

Good morning. I am new to coding VBA and need some help if possible. I need to import a Comma Delimited Text file into a MS Access table. The records vary in length and may take up multiple lines of text before the next record. The first field contains the type of record and are all prefixed 1###, with the ### being variable. (1001 - 1100). If the record goes over one line the 2nd line with start with 1000. Here is an example: The records with 1001 continue to the next line with 1000 so you know it continues. The other records are all single lines starting with 1100, 1003, 1004, 1006...1017.

1001,00000000,00000000,00000000,00108888,00537906
1000,00100,"CAJUN MEAT ","CHUB "-0020380
1001,00000000,00000000,00000000,00020492,00130534
1000,00108,"RED BEANS ","CHUB ",-0221510
1001,00000000,00000000,00000000,00222572,00796807
1100,0000000000,0009605419
1003,01,000101,263558519,"QQ380427",5,08,19,107,16,07,58
1003,02,000202,263774367,"QQ380428",4,08,19,107,22,22,12
1004,1," "
1004,2," "
1004,3," "
1004,4," "
1006,00000000,00000000,00000000,00000000
1007,"$ OFF ",0000000000,0000000000,0000000000
1007,"FREE ",0000000000,0000000000,0000000000
1007,"SENIOR 10% ",0000000000,0000000000,0000000000
1007,"50% Police ",0000000000,0000000100,0000000001
1007,"CREW 50% ",0000000000,0000000310,0000000003
1007,"CREW 100% ",0000000000,0000001976,0000000009
1007,"MANAGER ",0000000000,0000000260,0000000003
1007,"BONUS ITEMS ",0000000000,0000000000,0000000000
1008,00107,00001,00065,06074,00347,00290
1009,01,"BREAKFAST SALES ",0000114476,02940,000091
1009,02," SALAD SALES ",0000009093,00233,000007
1009,03,"DRIVE-THRU SALES ",0000142716,03666,000106
1010,0000002646,0000009093,0000000000
1011,000,0000000000,003,0000005208
1015,"NET SALES ","+",0000389258
1015,"TAX ","+",0000023434
1015,"GROSS SALES ","=",0000412692
1015,"RESTAURANT BANK ","+",0000000000
1015,"ADJUSTED GROSS CASH ","=",0000412692
1015,"SEPLINE----------------------"," ",0000000000
1015,"AMOUNT DEPOSITED ","+",0000365609
1015,"GIFT REDEEMED ","+",0000000000
1015,"CREDIT CARD ","+",0000044844
1015,"MISC. INCOME ","-",0000000000
1015,"PETTY CASH ","+",0000000000
1015,"NET SALES ","-",0000389258
1015,"TAX ","-",0000023434
1015,"CASH OVER/SHORT ","=",-000002239
1015,"CASHIER OVER/SHORT ","-",-000001839
1015,"OVER/SHORT RECONCILIATION ","=",-000000400
1015,"SEPLINE----------------------"," ",0000000000
1015,"NET SALES ","+",0000389258
1015,"GIFT CERT. SOLD ","-",0000000000
1015,"OTHER NON-FOOD ","-",0000000000
1015,"NET FOOD SALES ","=",0000389258
1016,1,00000000,00000000,00000000
1016,2,00006103,00036415,00118800
1017,0004339338

I will need to do this weekly for many different files. I appreciate all the help that anyone can provide.

View 4 Replies View Related

Queries :: Multiple Lines In A Field?

Jan 8, 2014

I've exported some data which has 3-4 address line within the same field which is very odd.

The row has all the usual data, but when I look at the data in datasheet view the adresss line 1 shows only but when I click into the field and press down, more address info comes up. Is there any way to have this on one row within the field rather than multiple.

A bit like in Excel when you Alt+Tab in a cell it drops down but is the same cell, well this exactly like that in Access 2003.

View 1 Replies View Related

Split One Record Into Multiple Lines

Mar 10, 2013

I have an access form that is used for cash-ups. The cash-ups are not done on a regular basis and the owner requested that the database look at the last cash-up date, current cash-up date and calculate the difference in days and then calculate the average take per day by dividing the total take for the period by the number of days from the last cash-up date to the current cash-up date. This was simple and is done however, what he now wants is to export this data to another table and have it split the total amount per record by the average number of days and reflect the average amount per day over as many lines.

Eg: R5,000.00 / 20 days = R250.00 per day.

This data needs to be displayed in TWENTY lines each with a value of R250.00.

View 1 Replies View Related

Sync CheckBox To Update/Write To Text Field

Jul 14, 2006

Hey Guys,

I'm looking to create a "Same As Billing Address" check box that would automatically udpate the address in an order to reflect the customer's billing address.

I understand how to sync a combo box to an option group ( -- great article by the way)

I Guess what I'm looking to do is:

1. Sync a "Same As" Checkbox to multiple text boxes in the form (Unit, StreetAddress, City, Province, Postal) such that these fields are UPDATED (writen to) with the same information as the billing address

2. [U]IF the "Same As" Option is selected, I need the updated fields to lock, allowing no further update... but must by unlocked if the "Same As" option is NOT selected.

Please help me out with this one guys.. I really need it.

View 2 Replies View Related

Queries :: Possible To Split One Table Into Multiple Lines?

Oct 27, 2014

My boss made a satisfaction database that tracks satisfaction in 2 ways, so we have:

Table

ID
SatisfactionAType
SatisfactionAScore
SatisfactionBType
SatisfactionBScore

I'd like to make a query that would split these into two lines, one for A, one for B and then export it to excel.

View 4 Replies View Related

Forms :: How To Prevent Multiple Lines In Field

Apr 13, 2015

Ive had a recurring problem with one of my databases

ive got a form with a subtable on it, the subtable is very important as much of the DB's data is entered in there.

every once and a while one of the users will be doing data entry and on a certain field (a short text field if that makes any difference) theyll push enter instead of tab, to move on to the next field, enter creates a new line instead of moving to the next field, when a new line is created the user will either assume the field is blank and retype the data or ignore it entirely and move on

the trouble is one of my queries completely fails to work when there are multiple lines in this field (comes with a data type mismatch error)

is there a way to prevent the field from being able to contain multiple lines (like a property setting or some VB code?)

View 3 Replies View Related

Help With Select Query To Get Single Line Instead Of Multiple Lines

Dec 21, 2007

Hi,

I have a query that selects values from a table and I'm further creating a letter based on this query. The problem i'm having is that if the query pulls 3 rows as result then it creates 3 separate letters. Basically this is how the output of query looks:

(refer attachment)

Is there any way to write this query so that I can get output as below:

Col1 Col2 Col3
10 20 10

Please advise.

View 7 Replies View Related

Multiple Lines In Order Form From Single Database

Feb 28, 2005

I have 2 databases Product, with 3 entries ProdID, ProdName and UnitCost.
2nd database Order has Order ID, ProdName, UnitCost, Quantity, SaleDate and Total.
I want to create a form to input data into the Order databse that allows me to enter several rows using the data from the product table by selecting ProdName(Product) from list and UnitCost(Product) being inserted into each row automatically as a new ProdName is selected.
Can anyone hsuggest how best to do this.

View 4 Replies View Related

Forms :: Duplicate Lines In Multiple Form And Query

Jul 8, 2015

The purpose of my database is to keep a record of training activities undertaken by staff. There is therefore a multiple form which displays, for a specified worker, the list of all the required activities. Each line has a button which opens another form to allow an activity to be started (together with start date and other info). The main tables are Worker, Activities, and a junction table Worker2Activities. On closing the second form and refreshing the first one, the started activity is correctly recorded, but on as many lines as there are staff on the course. To make matters worse, these also appear when i then select another worker (and if i create another worker, the number of lines goes up by one for all of them). The Worker2Activities table correctly show one new record created

View 3 Replies View Related

How To Keep Multiple Lines When Importing Data From Excel To Access

Sep 7, 2011

I face a multiple line problem when i try to import data from Excel to Access db. Is it possible to keep multiple lines of a cell after transferring to Access. Since Access see any ceel of Excel as a Field, it takes all the lines as a line in the same row.

In enclosed, you can see a sample Access document. I put a point(.) between every lines in the same field manually. Is it possible to split up those data from these points and make a new line in the same field by VBA? For example,

BEFORE:

1) 232N1635-2.2) 412N1168-7

AFTER:

1) 232N1635-2.
2) 412N1168-7

View 1 Replies View Related

Read/Write Queries, Multiple Joins...

Jun 30, 2005

Okay, take three tables.

AreaTable
AreaID
AreaName
Percent

GangTable
GangID
Area_ID
Speed

MasterTable
MasterID
Station
Description
Area_ID

The Relationships

AreaTable.AreaID 1---------> (inf) GangTable.Area_ID
AreaTable.AreaID 1---------> (inf) MasterTable.Area_ID

Initially the query below used Inner Joins, however that limits my list, when i want to see ALL elements from the MasterTable no matter what, thus my inner joins became left and right joins as follows.

Simple Query: Query1

SELECT MasterTable.Station, MasterStable.Description, MasterTable.Area, AreaTable.Percent, GangTable.GangID, GangTable.Speed
FROM (AreaTable RIGHT JOIN MasterTable ON AreaTable.AreaID = MasterTable.Area_ID) LEFT JOIN GangTable ON AreaTable.AreaID = GangTable.Area_ID;

This should simply display All Records in MasterTable, (Multiple Times if Necessary) listing all the elements of AreaTable that are Linked to the MasterTable, and all elements from GangTable that are linked to AreaTable. It does this, and displays them nicely. But I can't edit the fields. I get the result:

1 | Station One | Area 1 | 45% | 204 | 1000
1 | Station One | Area 1 | 45% | 304 | 500
1 | Station One | Area 1 | 45% | 404 | 750
2 | Station Two | Area 1 | 45% | 204 | 1000
2 | Station Two | Area 1 | 45% | 304 | 500
2 | Station Two | Area 1 | 45% | 404 | 750
3 | Station Three | Area 2 | 75% | 254 | 800
3 | Station Three | Area 2 | 75% | 354 | 600
3 | Station Three | Area 2 | 75% | 454 | 700

So you can see that Area 1 has multiple Gangs (204,304,404) and Multiple Stations (1,2). If you do a simple set up like this, you'll find that you can't change the Description field (Rename "Station One" to "Hello World"). It just doesn't work, no matter which way I've tried, I can't seem to make a Query that presents all the information from MasterTable and All the Information IN AreaTable and All the Information in GangTable which will allow me to also edit the fields.

Any Help would be most appreciative, I'm tearing my hair out on this one.
Thanks,
Jaeden "Sifo Dyas" al'Raec Ruiner

ps - It just seems that with Junction Tables and all the many to many relationship designs I have tried, you'd be able to change the non-related fields. I understand that you can't change the "ID" fields, but the others should be editable.

View 2 Replies View Related

Correct Syntax To Write Command Line Able To UPDATE More Than One Field In Table

Nov 24, 2014

what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.

Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:

TableI
A B C D
1 2
2 6 4 3

1 7 5 9
1 2
2 5 8 5
etc.

I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:

TableI
A B C D
1 2 & %
2 6 4 3
1 7 5 9
1 2 & %
2 5 8 5

I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:

Private Sub BtSalvarFrmII_Click()
CurrentDb.execute "UPDATE TableI"
Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2
Docmd.save
Docmd.close
End Sub

What would be the correct syntax?

View 7 Replies View Related

General :: Saving Invoice With Multiple Lines In The Item Section

Jun 17, 2014

I want to make an invoice with products and services and totals, the thing that has me wondering is how do I save an invoice with multiple lines in the item section of the invoice. I want to be able to choose a customer and then see the list of invoices connected to that customer, and when I click the invoice to open it as it was. What would be the best way to do this?

The invoice contains:Customer info, adress, name...etc

List - multiple lines of products sold / services completed
Totals, date & time...etc
Relationship goes like this: Customer -> Invoice <- Inventory / Services

View 3 Replies View Related

Write A Query That Will Return Records From Multiple Tables

Dec 19, 2012

I am trying to write a query that will return records from multiple tables. I currently get an error suggesting I create a subquery or else I get far too many records.

The query is EditAttendanceQuery (I left it in a bit of a state). The fields I need are shown in the query. The records I need are based on the Edit AttendanceQuery (Form). I can get the records I need without the CourseNumber and Section, but it all goes downhill when I include them.

View 14 Replies View Related

Import Text File / Multiple Lines And Varying Delimiters / Size To One Record

Aug 22, 2012

Using MS 2007, I have a 200 text files exported each day from another application that has two different types of lines (see below). I would like to import each text file in to a database as a single record.

Text file example (text.txt):

R111 WC 8/21/2012 7:00 Doe, John doej 10110110

First Question? Y
Second Question? N
Third Question? Y
...
Seventeenth Question? 10

As you see, I have the first row with multiple fields, but the next rows I have a question and an answer.

I would like to have this data imported as shown in the attachment. Example.zip

Most answers I see are for either multiple lines (same data and sizing). I am not sure how to handle several different lines with that vary in size and delimiters.

View 5 Replies View Related

Queries :: How To Write A Query Which Selects Multiple Records From A Table At Once For Updating

Aug 14, 2013

I have a list box whose data is inserted in a table named as "test0" ,now in a macro of vba i want to select all the items in the list box and create there pdf files in a folder at my desired location. So far i have managed to create a pdf file of single item ,but i want to select multiple items at once ,

Code:
SELECT test0.ID, test0.item FROM test0 WHERE (((test0.item)=[ItemNumber]));

So, in this query itemNumber are multiple and i want to create there pdf files at once ..just on a click of one button ?

View 2 Replies View Related

Forms :: Inventory - Allow Users To Update Multiple Fields For Multiple Assets

Sep 25, 2014

I want to create a form that allows users to update multiple fields for multiple assets. Below is what I came up with:

Ideally, I'd like the subform to be filled in by having the user select multiple Assets from the S/N combobox field which would then auto-populate the "Type" field. Then they would fill out the appropriate fields they want edited in the top part of the form. They hit save and magic happens. This would also be nice because only assets they want edited would be displayed (easier on the eyes) and no distinguishing would be necessary. To do it this way, I know I would need to use a temp table but I wanna avoid using temp tables.

I know I can do this by adding a Yes/No field in the "Asset" table, setting the "Asset" table as the subform's recordsource, and then putting a checkbox in the subform and allowing them to check the assets that they want to edit (which would also allow me to sort it instantly so that checked Assets are at the top of the datasheet for easy viewing), but I would like to know if there's a way of accomplishing this without the use of checkboxes.

I know I could also use a listbox and that allows them to multi-select items, but I'm not sure if that allows me to group all selected items at the top of the listbox for easy viewing of selected items. Plus it would involve a lot of scrolling (there are over 2k assets).

View 2 Replies View Related

Multiple Update Using Dao

Jul 5, 2005

ok, here is the code i've got...

Code:Dim DAOdb As Database Dim DAOrs As DAO.Recordset Dim sSQL As String Set DAOdb = CurrentDb() 'select the record corresponding with the value in the hidden textbox sSQL = "Select * from table where field =" & "'" & Me.textbox.Value & "'" & ";" Set DAOrs = DAOdb.OpenRecordset(sSQL) With DAOrs .Edit 'update this field in the table .Fields("Status") = "Complete" .Update End With DAOrs.Close DAOdb.Close
i want to be able to update all of the fields that match the textbox. as it is now, it only updates one. how can i loop through all of the records and update all of the ones that meet the criteria??

thanks for your help. It's greatly appreciated!

*j

View 2 Replies View Related

Multiple Update To Same Field

Sep 6, 2006

Hi, I have created a field called age, where I have a formula that takes the date of birth out of the IDENTITY NUMBER and calculated the peoples age, I have then created a field where I would like to allocate groups depending on their age, i.e, 18-25 = Group1, 26-35 = Group 2,etc... I am having trouble with this, firstly, I cannot get the formulas to work individually and secondly, I get an error that says I have duplicate fields to update.

PLEASE HELP!!!!

View 2 Replies View Related

Update Multiple Fields At Once

Sep 6, 2007

Hey guys-
I have 2 identical tables. I want to update the data from Table1 to go into Table2. Each table has well over 70+ fields in them. Instead of handwriting out each [Table].[Field] in either SQL or the Designer- is there a shortcut to tell Access to grab all the fields from Table1 and update all the fields in Table2 (all the fields have the same name)? I just don't have the energy to type it all out- I figure there's got to be a way...

I know when you do an APPEND query in the designer- it will do this for you- but not the UPDATE query...
Thanks!

View 6 Replies View Related

How Can Update Multiple Records??

Sep 13, 2005

Hello,
I would like to update multiple records in one query.
The scenerio is that i have 3x3 (3 in row and 3 in column) text feilds and i want to update it once, in one query after entering the data in text fileds.
Can anybody help me please......????

Waiting of your quick reply.

View 3 Replies View Related