Modules & VBA :: Adding FlexGrids - Freeze Calculated Fields

Mar 24, 2014

I have to build an estimate worksheet from scratch on Access I am almost finished with the project but I have a few questions.

(SEE ATTACHMENT FOR CLARIFICATION)

1. There is a labor section on the form where the user selects the combo box adds the quantity of workers, and number of hours. The output would be what the total amount needed for that trade. On a different table i have the wages of each trade per month that is associated to the form. My problem is if there is a new wage amount for a trade it will affect all my previous records and i do not want that. Is there a way i can set it to only change my new records. In file maker there is something called defined record that is a solution for that program but i was wondering if i could do the same for access or Visual basic application for access. Another solution that I had in mind was like having a button where user could click and all field’s data will not be changed in the future.

2. There is a material section on this form where user input cost of materials. Once user is finished with data entry they click on a button that generates another form for presenting/printing purposes. The thing is that not all fields are always used i wanted to know if there is a way on to adding a flex grid so there would take less space up?

View Replies


ADVERTISEMENT

Now () When Adding (form), In A Table NOW () Must 'freeze'

Jan 8, 2006

When I add something in a table using a form, I always want an automatic date, namely today's date. So I set this in the form with NOW (). The only thing is, when I view my records a day later, it automatically adjusts the dates. I don't want to type in the date every time, because it's always todays date. But I don't want the table to adjust the data-capturing dates automatically.Any suggestions to get around this?

View 3 Replies View Related

Modules & VBA :: Adding Calculated Field To Existing Query

Nov 12, 2014

I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?

The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.

View 6 Replies View Related

Modules & VBA :: Freeze Panes In Excel From Access

Aug 20, 2014

I need to freeze the panes in an Excel spreadsheet form within Access. I have the following code:

Code:

Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh As Object

Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Add
Set xlWSh = xlWBk.Worksheets(1)

With xlWSh.Range("B5")
ActiveWindow.FreezePanes = True
End With

The codes runs, but the panes are not frozen. (I got this from the Excel macro recorder)

I need both panes frozen. Where is my error?

View 5 Replies View Related

Modules & VBA :: Calculated Date Fields In Tables

Jul 29, 2014

I want to use an expression to compare 2 dates and calculate the date of the last set of accounts for a company. The user will enter(DD/MM) of the company year end e.g. 31/12. I then want to compare this with todays date (in another field) to ascertain if the month has already passed in the current year and hence calculate the last year end. i.e. If (current month>company month end), year end date is DD/MM/YYYY (where YYYY is taken from today's date), year end date is DD/MM/(YYYY-1). I am not a programmer and although I understand database theory (from the teaching perspective) I am not expert in developing databases.

View 1 Replies View Related

Modules & VBA :: Adding And Editing Records From Unbound Fields

Mar 4, 2015

I total novice at VBA. I am trying to code a button to modify (the last) record in a subform list and then add a new record based on values in unrelated or unbound fields on the button form.

The following code is based on the first of two YouTube tutorials (this bit on the edit) and looks like it should work. Except that my Access 2010 with Visual Basic for Aplication v7 does not recognise the type definition Database or Recordset

Code:
Private Sub ANOwner_Click()
Dim cn As Integer
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb

[Code] .....

View 6 Replies View Related

Tables :: Adding Icon To Calculated Field?

Aug 17, 2015

I'm currently building a database and one of the things that I must make is a "warning", more like a symbol on a continuous form, that is there, if the task is wanted by a costumer.

I have a Yes/No field in database, which decides that. Now I need to figure out how to make it appear on form. I thought about making a calculated field, that would display some warning icon.

Now my question is, is it possible to add an icon to a calculated field and if possible, how?

View 4 Replies View Related

Queries :: Adding Time Elapsed That Was Pre-calculated

Feb 24, 2014

Here is the table info I'm working with:

class status changeby changedate statustracking
INCIDENT-RESOLVED- Person A-2/20/2014 9:05 -
INCIDENT-RESOLVED-Person b-2/20/2014 11:57 -
INCIDENT-QUEUED-Person b-2/19/2014 13:57 - 01:24:08
INCIDENT-QUEUED-Person c-2/19/2014 15:21 - 19:29:58
INCIDENT-QUEUED-Person c-2/20/2014 10:51 - 01:06:00

I need to be able to add the statustracking time together per person per status (so person c would need 19:29:58 + 01:06:00 added together). Access keeps trying to add dates to the field or reconfigure the numbers to date ime or zeros if I use any of the data type other then Memo or text.

View 1 Replies View Related

Reports :: Hourly Rate - Calculated Totals Not Adding Up

Sep 5, 2013

We recently have had a change to our hourly rate that we pay and now the Totals calculation is not adding up the numbers correctly.

In the query that the report is based from:

ExtendedTotal: CCur([Hours]*[Rate])

In the footer of the report: (Provides a Total for each Day)

Sub-Total: "Control Source" is: =Sum(CCur([ExtendedTotal]))

Report Footer: (Provides a Grand Total for the Month)
Total: "Control Source" is: =Sum([ExtendedTotal])

Problem comes in with the odd dollar rate time partial hours. For example:

$9.73 x 4.5 hours = $43.785 (Rounds to $43.79) which is fine...

but when you have a whole column of these rounded numbers, the totals are coming out off by pennies which add up to a good bit at the end of the month. I cannot match up the totals with the invoices that are coming in either.

View 14 Replies View Related

Freeze Form

Jul 14, 2006

Hey Guys,

Well i have a table with 39 observations. I made a form that updates the data of thid table. So the next values would be the observation number 40. What i wanted to do i that my form only showed the field that is not filled up, in this case would be the 40. Its like the form shows the 40 observation in blank and lets me fill up. After i fill it up it should only show me the 41 and so forth. And also block all the previous form editing.

Can anyone help

Thanks a lot :)

Kind regards

View 14 Replies View Related

List Box - Freeze Columns When Scrolling

Jun 3, 2005

HI

I have a list box which sometimes needs extensive scrolling to the right to see other data.

Im wondering whether I can freeze the first few columns (key info) so I can trace the other data when scrolling. (i.e. like in an Excel spreadsheet)

I dont mind what coding if I have to - any suggestions please?

View 2 Replies View Related

Freeze/lock One Record In Subform

Dec 14, 2004

hi! I asked this question before, and i didn't understand the answer.
I have a form with a subform. at first, i disable the subform until the main form is filled completely. Then, I have a button to enable the subform. This button also disables the main form.
My first line in the subform needs to mirror the info in the main form. To do this, i open a recordset, and then use .AddNew to fill in the details.
I don't want this first line to be edit-able or delete-able once it has been filled. How do I lock it from being edited? Also, if I later decide to delete everything on the form, will i be able to clear the whole form if this line in the subform is locked?
Please helpp

View 4 Replies View Related

Forms :: Adding Fields In A Table And Having Output Fill Fields On A Form

Jan 29, 2014

I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.

Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));

That is the query.

View 14 Replies View Related

Freeze Panes - Similar Action On Access Form

Apr 12, 2005

Hi there peeps

I would be really grateful if anyone could tell me if it's possible to have a horizontal scroll bar on a form that only moves part of the form's contents... similar to the freeze panes option in excel.

Obviously vertically you can use the form header and footer, but there doesn't seem to be anything horizontally that is equivalent.

Many thanks in advance,

Chris

View 1 Replies View Related

Forms :: Switching From Design View Causes Access To Freeze?

Dec 30, 2014

Access 2013 on Windows 7 ..I have a Main Switchboard form which is Pop-up and Modal. Everything on it is working as required, but if I am in Design View and try to switch directly to Form view, the form disappears and the Access screen freezes - the mouse moves, but no menus or objects can be selected. If I have the VBA window open, I can carry on working in it, saving the modules and closing that window as usual. But the only way to open a form or do anything else in the main screen is to kill Access by closing the Access window in the system tray (or using the Task Manager).

If I close the Switchboard when in Design view and then open it in Form view, everything's fine.

The only recent change I can think of is upgrading from a 32-bit implementation of Access to 64-bit.

View 4 Replies View Related

Tables :: Deleted Fields Not Free Up For Adding New Fields

Aug 15, 2013

I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?

View 8 Replies View Related

Calculated Fields

Sep 13, 2006

Hi,

I have read that it's not recommended to put calculated fields on a table and they should instead be calculated using a query.

How about if I need to store that calculated value in a table (e.g. field1 + field2 = value in field3)

How can I do that please?

Thanks,
B

View 5 Replies View Related

Calculated Fields

Mar 26, 2008

I am trying to create a calculated field in a query, that shows money owed from customers. I have 4 different job codes that have their own prices. I also have customers with the amount of hours they owe for, for each job. There are two customers that owe for two different jobs. I know how to write in : Money Owed: [price per hour]*[number of hours]. but i want to have only one line for each customer when i run the query. How do i combine??

View 1 Replies View Related

Sum Calculated Fields

Aug 8, 2006

I have a detail form, which has controls populated from a query.

For example,
Name Number
xxxx xxxx

This can be any length depending upon the amount of database records, so there might be 5 names, or 4 names, etc.

I then need to sum these in the footer. Is the possible?
I just seem to get #Error displayed when doing SUM([ControlBoxName])

View 9 Replies View Related

Calculated Fields

Sep 30, 2004

I have a calculated text box on a form:-

=[AnnualSavings]/[SKUAnnualSpendInPounds]*100

But, if both the Annual Savings and SKUAnnualSpendInPounds text boxes have a value of 0 the result of the calulation comes out as #Num!. I assume this is the error because I am doing 0 divided by 0.

Does anyone know if this is the reason for my error and if so is there anyway of changing the #Num! back into a 0 so that I can submit it to my tables.

Any help would be greatly appreciated

View 1 Replies View Related

Calculated Fields

Apr 27, 2005

Here is my dilemma

I have a table called inventory that stores my different products and the quanitities i have in stock. I also have an orders table that handles a customer palcing an order for a certain product and the amount they want. What i want to happen is this: If a customer orders for example 5 widgets, I want my inventory table to automatically reflect the 5 widget deduction from the quantity in stock. Anyone know how to do this?

View 1 Replies View Related

Calculated Fields

Mar 13, 2008

Im wondering how to make calculated feilds in a query?
Do i have to make the feild in a table the go to query?

View 1 Replies View Related

Manipulating Calculated Fields

May 18, 2005

I developed a query that calulates when a contract is reviewed i.e 5 weeks and 9 weeks after the commencement date. These are called CDE1 and CDE2. This works well and the CDE1 and CDE2 dates are displayed ok.

The query above is:
SELECT Contract.ContractNo, Contract.APSNo, Contract.Firstname, Contract.Surname, Contract.AgencyDesc, Contract.FacilityName, Contract.Designation, Contract.WeeklyHrs, Contract.PositionNo, Contract.ComDate, Contract.EndDate, IIf(DateAdd("ww",5,[comdate])<[enddate],DateAdd("ww",5,[comdate])) AS 1stCDE, IIf(DateAdd("ww",9,[comdate])<[enddate],DateAdd("ww",9,[comdate])) AS 2ndCDE
FROM Contract;

The problem is that I now want to access contracts under these CDE dates between two dates that is user defined (startdate) and (enddate).

As the above are calculated fields, how can I develop a new query based on the above as CDE1 and CDE2 are already calculated fields?

View 2 Replies View Related

Storing Calculated Fields

Aug 9, 2005

New to Access and learning the hard way. Im sure this is an easy one, but sure would appreciate the help. I am trying to sum a group of cells and display the total, which i have done. But I also want to store this total in my database. So my question is, how do i move my calculated total to my field that has the "record source" bound back to my field in the database? Hope that makes sense. Thanks in advance.

View 3 Replies View Related

Refreshing Calculated Fields

Sep 10, 2004

I have an Access 97 database with a form and a subform for invoice and invoice details. When I change the qty or price in the subform I have a calculated field on the main form that updates. I also have a button that when clicked brings up the payment screen with the totals from the previous screen. The problem lies when I change the qty or price and click the button right away, the next form pops up but does not take the new totals, it grabs the old ones. The calculated fields do not refresh fast enough. I have tried requery, refresh, and even a loop to make it wait before opening the payment form, but nothing seems to work. I'm at a loss. Any ideas would be appreciated

Thanks
Ken Reid

View 1 Replies View Related

Calculated Fields In Forms?

Sep 9, 2006

Now I can create a Query that adds, subtracts a specific column, but im having a hard time trying to get the information into a form since it don't seem to allow me to pick the query field which is calculated as a datasource. Can someone tell me how to make a calculated query column appear on a form as a field? Such if I type in monday in the regular table, the form will add a few days to the next available game? I have the information/code already in a query that displays the query right in a new table using the Expr method but I can't get the form to just simply display the field. Someone please help!

I see a few people asked close to the same question but nobody never answered so I am thinking it is impossible

View 3 Replies View Related







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