Form Prefill-> 2 Rows?

Dec 13, 2005

I have an invoice form - selecting a unique CONTRACT number for a completed ORDER, prefills the subsequent fields in that row with the contract information / price etc. (see below)
The VBA code retrieves the appropriate recordset and prefills the controls with the appropriate values.

If Me.NewRecord And Not IsNull(Me.Controls!Job) And Me.Controls!Job <> 0 Then
With Me
Set rs = CurrentDb.OpenRecordset("SELECT DRG.DrgNo, ORDERDETAILS.Price, DRG_DESCRIPTIONS.Description " & " FROM DRG_DESCRIPTIONS INNER JOIN (DRG INNER JOIN ORDERDETAILS ON DRG.ID = " & _
" ORDERDETAILS.DrgId) ON DRG_DESCRIPTIONS.ID = DRG.DescriptionId WHERE ORDERDETAILS.JobNumber= " & Controls!Job)

If Not rs.EOF Then
Controls!DrgNo = rs![DrgNo]
Controls!Desc = rs![Description]
Controls!UnitPrice = rs![Price]
End If
End With

However, a contract may have additional costs incurred and as a result there may be additional invoice line items to consider for the invoice totals. If the above recordset contains more than one record for the selected CONTRACT (ie. there are additinoal charges to cost) - how can I prefill the contents of more than one sub-form row ie. is it possible to force the current record pointer to the next record and prefill with the additional charge info?

Thanks
Guido

View Replies


ADVERTISEMENT

Accecss Auto Prefill Based On Table Data?

Jul 7, 2006

I want a field to be auto filled based on the data entered into another field. i have a street name table which has 3 fields, the street name, a colour code field assigned to each street and a grid reference field. I have a benificiaries table which i want to be able to enter the street where they live in the street field and have the colour zone and grid reference fields auto-fill by referencing the streets table. Seems simple but i have no idea how to do it - any ideas.

View 1 Replies View Related

One Form Multiple Rows

Dec 31, 2004

I want to use a form that will put info from different drop down fields into the same column of a table.

Example:
(The information of the drop down boxes comes from table TopicNames)

Form: Caller
topic 1 topic 2 topic 3
wheelchair dancing Alzheimers behavior


to table "Topics"
wheelchair dancing
Alzheimers
Behavior

When I select the first topic the others change to the same

topic 1 topic 2 topic 3
wheelchair dancing wheelchair dancing wheelchair dancing

What do I need to do to make the form accept different input?

View 2 Replies View Related

Compare Several Hundred Rows Against Other Rows

Nov 21, 2005

Not sure if what I want to do is possible, or at least possible the way things are set up.

I have a massive table - c. 6 million rows. It contains data along these lines:

Plan#, Item, Price, Description, Colour, Value, Location, etc.

The primary key would be Plan# + Item. Each Plan# has approximately 1,000 Items, and there is only 1 Item per Plan#. There are only a limited number of Items (c. 1500) and all or only some Items might be assigned to the Plan#. All items under each unique ID# belong together, sort of in a set. So this huge table has approx. 6,000 unique sets (based on Plan#).

To add to the confusion, Item A under Plan#1 may have different information (Price, Description, Colour, etc.) from Item A under Plan#2. I know this isn't a great way to set up data but this is what I have to work with.

Over the years it's possible that the exact same combination of Items with identical values might have been set up for multiple Plan#s. What I need to do is find any Plan#s which have the exact same combination of Item, Price, Description, etc. So if Plan#R has 200 rows and Plan#S has 201 rows, it automatically doesn't match. If Plan#R has 200 rows and so does Plan#T, all information in each record must match between the two Plan#s (with the exception obviously of Plan#).

I don't think this is possible, and if it is I am sure it's not going to be easy. So far the best I can do is to come up with finding duplicates on Item, Price, Description, etc. but that's only one record at a time and doesn't tell me if the two Plan#s match.

Any help or suggestions would be much appreciated.

Thanks!!!

View 3 Replies View Related

Form Populates Two Rows In Table

Mar 13, 2008

Anyone know why the following would happen:

I have a form that is linked to a single table. For some reason some controls populate one line in the form and others populate another line.

Each time I complete the form it creats two records with some data in one row and other data in another row.

View 1 Replies View Related

Split Form Input Into Multiply Rows

May 14, 2007

Hi, I have a problem, I want to split up form input into several rows but I don't know how. Alright here's the deal: I have a textbox in a form were I write down all the ipaddresses for one computer and all the addresses end up in the same row in the same field in the table, I want the addresses to end up in different rows but in the same field, how do I do?

I might also add that i'm pretty new to access.

View 2 Replies View Related

Color Alternate Rows In A Continuous Form

Oct 25, 2005

Hello,

Does anyone know how to color alternate rows in two different colors in a continuous form?
:(

View 1 Replies View Related

Access Data In Multiple Rows In A Form

Feb 23, 2006

Hi guys ,

I'm very new to Access , but I need to do this and I can't figure it out. I have a form that looks like this :

http://img64.imageshack.us/img64/5566/formemail4qz.jpg (http://imageshack.us)


I'm trying to add up all the emails in a single string , but I can't access the records one after the other. The best I can do is display the currently selected record's adress ( The one with the black arrow in front ) . I tried browsing through the Fieldname.Value propertie ( in this case Me.Courriel.Value ) . I though I could cycle through all the Controls in the form , but Me.Controls.Count returns 3 , so I figured all the records must've been embedded into one control. Can anyone help me :( ?

(Sorry if this is a documented question , I've tried searching but couldn't find anything )

View 3 Replies View Related

Set Colors On Specific Rows In Continuous Form

Sep 5, 2006

Can we set different colors on specific rows in a continuous form?
Suppose the following data entry form (continuous) is to fill in spouse name, while name and marital status is already prepared in a different table. If the marital status is Married, then the spouse name textbox's background color will be Yellow. The standard background color is white.

Name Marital Status Spouse Name
-------------------------------------
xxxx Married (Yellow)
xxxx Unknown (White)
xxxx Not married (White)
xxxx Married (Yellow)

View 1 Replies View Related

Modules & VBA :: How To Get Recordset For Empty Rows In Form

Oct 14, 2014

Are new, empty record in form have recordset presentation, how to get recordset in form for empty row?

View 1 Replies View Related

How To Return Specific Rows In A Query To A Form

Dec 31, 2012

I have a Query of dates and I need to have those dates return onto a from horizontally?

View 1 Replies View Related

Another Newby Question - Linking Multiple Rows To One Form

Jun 9, 2005

I have this form which shows an order.... linked to this order are the client, the principal company, and the company that carries out the order. The information for these three companies are all stored in one table (since the information is very similar).

Tables look like:
tblOrder
- OrderID
- ClientID * linked to CompanyID
- PrincipalID * linked to CompanyID
- Contractor (company that carries out the order) * linked to CompanyID
- Date
- ...

tblCompany
- CompanyID
- CompanyName
- CompanyAddress
- ...

An example is for instance:
tblOrder
- 1, 1, 2, 3, 01-01-2005

tblCompany
- 1, Comp A, 200 A Lane, LA,
- 2, Comp B, 100 B Lane, NY
- 3, Comp C, 300 C Lane, SF

So, on the form it should read:

Date of contract: 01-01-2005
Client: Comp A - 200 A Lane, LA
Principal: Comp B - 100 B Lane, NY
Contractor: Comp C - 300 C Lane, SF

I understand how to make one link to a form... but how do I make three links from the same table?!?!?

Any help is appreciated!
Jazz

View 1 Replies View Related

Automatically Highlighting Multiple Rows In A Subquery On A Form

Dec 3, 2004

Hello!

I have a form with a subquery on it that shows all of the records that the form is based upon. The subquery and form are linked already, so whatever record I change to on the form, the subquery will follow. The records are chronologically ordered by date in ascending order. Depending on what day the form is on, I want to be able to highlight the rows/records of that particular week automatically in the subquery. I was just wondering what method I can use to do the highlighting/selecting of the records if there is such a thing. Hope I was clear...THANKS! =)


G

View 1 Replies View Related

Forms :: Looping Through A Form To Clear Each Field For All Rows

Sep 4, 2014

How can I loop loop through a tabular form to clear each value in a field of all rows ? I tried the below code, but it did not work.

Loopcnt = DCount("*", "Budget")
For Loopcnt = 1 To Loopcnt
[Forms]![Budget Form].[Newbudgt] = Null
DoCmd.GoToRecord , , acNext
Next Loopcnt

View 8 Replies View Related

Forms :: Show Single Form With All Rows From A Table?

Jul 11, 2015

I'm building a simple access database to be used in a factory. It's pretty much there now, in the sense that it does all it needs to do, but some things feel a little "clunky". One of these things is the page where new products are created.

When a product is created the user has to specify what raw materials it's made up of and that works great, there is a continuous sub form that lets the user just add a row for every raw material. Having it as a continuous form makes sense as there can be lots of rows or just a few, so it's flexible.

Currently though the labour/time on that page works in the same way. But it feels a bit silly as there are only 3 sorts of labour (at least currently, they might add a couple more later). The labour types are..

- Build
- Finishing
- Handling

My question is, how would I go about turning the existing continuous row-by-row subform for labour into a single form that just listed those three types of labour (read from my labour type table)? So that it just shows a row that asks for quantity of each available labour type.

Not all products have all types of labour but it would be fine to write a 0 in the labour table for (for example) Finishing against a product that for example didn't need Finishing Labour. Obviously with the current continuous form approach no row would be written for Finishing if the user didn't select that.

I guess I could hardcode a single form to show the three current types, but ideally it would be flexible, coping with new labour types when the users find they need to add them.

View 5 Replies View Related

Forms :: How To Create A Form That Inputs Multiple Rows

Mar 18, 2014

I would like to make a form that can insert more than one row at one time. Something like add first column, then ask the user how many of the second row they would like, then prompt them for what is in the second row then add the information for the rest of the columns and have a separate row for each of the second column. So every row with have the same first column, but from there have a different row for how ever many desired in second column. So lets i enter for the first column, ABC, then I wanted 3 rows with ABC, then prompts me for the rest of the information for each of those rows separately.Something looking like this, oh and it would be updating an already existing table.

ABC|asdf|asdf|hgaf
ABC|hs|hasd|auio
ABC|JKL|ASE|ASDF

EDIT: I would also like to know if it is possible so it does it in ranges and dont have to do it manually like you enter the first column then enter a range for the second column and a bunch of rows are made with each value in between the range that was specified.

View 1 Replies View Related

Modules & VBA :: Update All Rows Within Column Using Value Entered On Form?

Nov 7, 2013

I'm trying to update all the rows in a column (column A, PO Number) within a table (iSupplierTable). The value (txtPONbr) is entered by the user on a form (NewPO).

Code:
Private Sub cmdSubmit_Click()
On Error GoTo cmdSubmit_Click_Error
Dim db As Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "iSupplierTable"

[code]....

View 9 Replies View Related

General :: Update Table Based On Form - Create Multiple Rows

Apr 15, 2014

I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.

I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.

View 3 Replies View Related

Converting Rows To Columns And Columns To Rows

Jul 31, 2013

I am trying to convert a table that looks like the following...

Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff

I would like to covert it to....

Customer 000462 000224

Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff

I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it.

View 12 Replies View Related

UPDATE Rows

Sep 11, 2005

Hi Im trying to update a row this is the SQL im using

UPDATE HouseEntry
SET [Date]=(SELECT [Term Dates].Start FROM [Term Dates] WHERE [Term Dates].Term =1)
WHERE
EntryNo=40;

When I run it I get an error saying "Operation Must Be an Updatable Query"

If I physically put it in the date e.g SET [Date]=#09/15/2005# It works
so there must be a problem with my Select Statement.
Any ideas???
Cheers
Bikeboardsurf

View 4 Replies View Related

Repeating Rows

Dec 25, 2005

hi
my problem is when i export data form table or query to textfile it give me repeating rows my computer is win me
help me

View 2 Replies View Related

Individual Rows

Mar 23, 2007

Hi,

refer to the (record.jpg)

the whole record is identified as " rst.RecordCount " ( red color box).
how to identify each individual rows ( blue color box)?

View 2 Replies View Related

Run Out Of Table Rows

Jun 1, 2007

The following is for a successful pet grooming business.

I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed

View 10 Replies View Related

Max # Rows In A Table

Jun 14, 2007

I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?

View 4 Replies View Related

Yes/No Boxes In 2 Rows

Mar 11, 2008

Hello there,

I am making a roster for 25 people. In my form, the start of the roster is 0700hours until 1900 hours (increments of 15 minutes each) so I have 48 check boxes, which are ticked when one is rostered on for that time.

I am getting crossed eyed looking across the screen at these 48 tick boxes each for Monday through to Friday.

I have searched high and low and cannot find anything on how to have 24 check boxes (7am to 1pm) in one row and the other 2 dozen (for the next 6 hours until 7pm) on the next row, to be less painful on the eyes.

Can this be done?

I would be grateful for any ideas on how to start.

Thank you.

Regards,
Ligaya

View 11 Replies View Related

Selecting Rows

May 17, 2006

Hallo Everyone,

I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.





aravind.s

View 1 Replies View Related







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