My form has a button that has a calculated field on it (a dcount(...)). When the button is clicked, a couple of conditions are checked and if all is ok, it runs the vba code. If the tests fail, it returns to the form without running the code. All works correctly, except that the dcount number has disappeared. I finally figured out that it is now hidden behind the button. How do I bring it forward again programmatically?
View RepliesTable1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
Is it possible to make a calculated field in a table not allow duplicates?
There is obviously no option to select No Duplicates in the field settings?
I have a calculated field which consists of a few other fields concatenated, i.e. [field1] = [field2] & [field3] & [field4].
I need field 1 to be unique when its concatenated, but fields2-4 don't need to be unique. Is there a way to do this?
In the zip code field of my form, if the first number of the zip code is a zero, such as 02436, the zero is deleted as soon as I leave that field. The zero won't stay and I want it to so I will have a valid 5 digit zip code. In other words, as soon as I leave the field the zipcode is changed to read 2436 - without the zero. How do I fix that? Thanks.
View 2 Replies View RelatedWhen I try to edit fields in a subform the cursor always jumps to the first record's first field with a tab stop. I can not complete the modification and the cursor jumps back to the first record. When on the first record and on any other field the cursor will jump to the first field with tab stop.
I use Access 2003.
I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
View 3 Replies View RelatedI have a database For customers of the company, when you open the main form through the search form , there are a filter . when you try to move between orders of this customer, there are a mssage (Asking you to approve of movement between the customer orders). to this point no problem, when you approval to move , cancel the filter on the main form with a stay at the current record (current customer).
View 14 Replies View Relatedis it possible to have a form page stay open for a time limit and then automatically close and open another form?
Reason I ask when a user clicks to open the database I want a splashscreen stating terms of use etc to stay open for a few seconds before it opens into the main database.
My form is a datasheet with 2 fields: "Product" and "Quantity". Obviously I can't add a calculated field in this type of form; however I would like the word " Total" to display in the "Quantity" field just under the last record. I would also like the sum of the "Quantity" field to display at the bottom of the "Quantity" field in the empty record below the last record.
View 14 Replies View RelatedI have a simple access database which up until now was working as it should. Then i made a Backup of the database Named it something different deleted certain data from the backup and when i went back into the original DB i now have #Name? where it used to calculate 2 fields.
View 1 Replies View RelatedI am struggling with the DateDiff fuction in a calculated field. After some study, I have used several variations of:
=(DateDiff("d",[DUED],Date())/7)
to calculate the time (in weeks) until a due date starting from today's date.
It continually turns up a specific date in the late 18 and early 1900's. It does not give me the number in weeks which was what I was hoping for.
Earlier I used a very simple: =DateDiff("m",[Birthdate],Date()) to calculate the age from a date of birth perfectly with no problems.
I do not understand why the formula is not working. Does it have to do with format or the fact that I am working with a future date and how can I rectify this problem?
I'm trying to sum a couple of columns from a subform datasheet and carry them onto the main form.
My main form, [frm_tours_details], holds subform [sbfTours] which is a datasheet.
On [sbfTours], I've got a couple calculated fields within the datasheet, one, called "txtStudentSum", which calculates number of people total ([students]+[adults]+[numfreeteachers]) and one, called "txtptotal", which calculates fee for the program based on the following formula:
Code:
=IIf([txtMonth]>=10 Or [txtMonth]<=4,([Students]+[Adults])*7,([Students]+[Adults])*8)
(The fee is $8/person in season, and $7/person out of season.)
I've been following online directions I found to put a text box in the form footer which then sums each of these calculated fields ("txtStudentSum" & "txtptotal"), and then reference these text boxes from my main form.
However, the main form text boxes just come up as #Name? or #Error when I try to reference these boxes on the subform. Or, sometimes, the one calculating total students will show the total of ALL students, adults in the database, not just those for this particular record-but only, it seems, if I have the subform open separately first, then load the main form. I also tried putting the whole original calculations into text boxes on the main form, but they will only reflect either the current record selected in the subform, or they will only show the first record, regardless of what you've selected, depending on which way I tried.
I've attached a copy of the database ... click on the tab 'tours', then click on, say, tour #6 (since this one is multiple programs and needs to calculate) to open the tour details form.
I have a main form with several with four subforms. On the main form I have a bound text box [ShiftTotal] I have on each subform a unbound text box that sums a field [ExtTaxIn]. I am working with just one subform till I can get it to work correctly. The code I am using on the after update event is as follows.
code
Private Sub TxtSoldQty_AfterUpdate()
On Error GoTo HandleError
Me.ExtTaxIn = (Me.TxtSoldQty * Me.TaxIn)
Me.ExtPrice = (Me.TxtSoldQty * Me.Price)
Me.InvSold = (Me.TxtSoldQty * Me.UnitOfSale)
Forms!frmShiftMain!TxtShiftTotal = Forms!frmShiftMain!TxtRunningTotal
Me.txtExtTaxIn.Requery
[code]...
This sort of works but the [ShiftTotal] on the main form is always one table row behind. I think this is because of the order the event fires.
I have a form with three fields named 'quantity' (numberfield), 'service' (combo box) and 'amount' (calculated field with controlsource =[service].[Column](2)*[quantity].
I try to sum the amount field in the formfooter with =Sum([service].[Column](2)*[quantity]) but I receive an error.
Somehow it does not get the value for [service].[Column](2)
How can I do this?
The table for the form is called customer_detail
I've got a subform (simple table view) inside a main form, the subform (sfrmRecipes) just has an IngID field (which stores ingredient ID's) and a percentage field
I've added a few calculated fields to this subform, to assist users in making decisions, however each of these fields needs a sum total
So far my percentage field (which is an actual stored field in tblRecipes) is the only one with a functioning sum
for some reason the two calculated fields (which are both plain numbers, no text) do not give me a sum total option, i am only able to use the count function on them
Why calculated fields dont sum up and if there is an easy way to get it working (tried creating my own text field in the footer and manually typing SUM(fieldname), but it does not work).
A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?
View 1 Replies View RelatedI have a form that is displayed continuously and there is a text field in it (Text12).In the footer of this form I would like to add a textbox that is the sum of all Text12's on the form.I tried =Sum([Text12]) in the textboxes control source but that is just displaying an error when I run the form.
View 2 Replies View RelatedI have an unbound calculated field [Txt1] that I would like to show the result as currency. The fields in the calculation are:
[CR] number, currency
[Commission1] general number, two decimal places
[Commission2] general number, two decimal places
The calculation is: CR15W * (1 - Commission1) * (1 - Commission2)
If I just set the field to currency it works fine. But I want the field to show the following:
"Some Text" & " " & Format(Calculation, "Currency")
When I tried to include the format it to currency I get type mismatch, run time error 13.
So I thought that perhaps I should try the following just to see where my error starts and I get the same error message when I use:
Dim Val1 As String
Val1 = CR15W * (1 - Commission1) * (1 - Commission2)
Txt1 = Format(Val1, "Currency")
Everything I have read says the formula is:
Format([Calculation], "Currency")
Is correct. So why won't it work for me?
We have a recent issue on client PC. After application running for a while, all the sudden the calculated field on the form not showing the details, though there is data on the control. After restart the PC, the calculated field display correctly. We guess this may be due to the theme we use.
View 3 Replies View RelatedI have a subform embedded in a main form, the subform has a field (text box) that is doing automatic calculation so no manual entries are needed from the user. I have tried to pass automatically the final result to a text box in the main form, but all events are not working (afterupdate, On change, On Dirty), etc.
I have a button to do the above mentioned in the main form, but I don’t want to use that button, I want to do all the process from the subform.
Also, from the subform I was able to do click on the button on the main form (programmatically), however MS Access is too quick and the value extracted from the subform is always empty (zero); Acees is finishing before the calculation is done.
I am using MS Access 2010.
I have a "Main" form called frmProjects that sports multiple tabs. One of those tabs is labelled "Milestones", into which I inserted a datasheet subform called frmSubMilestones. The table that feeds the subform is tblMilestones. I established the parent / child relationship between frmProject and frmSubMilestones, and everything is working just fine...
To summarize: frmProject as main form -> Milestones Tab on main form -> frmSubMilestones as datasheet subform on the Milestones Tab (there are other tabs that are not subforms).
I have been asked, for each milestone in the frmSubMilestones subform's datasheet, to capture the number of days spent by employees on a monthly basis. The Milestones table and the PersonDays table have a primary key and a foreign key that are similar.
To summarize: frmSubMilestones as subform -> txtAggregatePersonDays as calculated Textbox fetching data from tblPersonDays -> OnClick event -> FormLoad of frmPersonDays as datasheet PopUp form.
DoCmd.FormLoad is supplied with the usual parameters to make the PopUp form appear, filtered on the currently highlighted milestone. Everything works fine so far... well almost.
Problem 1: When I introduce new rows in the frmPersonsDays datasheet, everything seems fine at the surface, BUT the primary key of the calling form (i.e. frmSubMilestones) does not appear in the tblPersonDays table; this is normal because there is no form-based parent / child relationship between the two forms. As one might expect, I am trying, from frmPersonDays, to get the primary key value from the "parent" frmSubMilestones using the Forms collection. No dice. I surfed and surfed, tried the bang and the dot operators, drank scotch, but nothing worked. I had to add foreing keys manually in tblPersonDays to further my tests. I can't figure out how to reach any control on the calling form, which is a subform.
Problem 2: The calculated txtAggregatePersonDays on the frmSubMilestones works wonders for existing data in tblPersonDays. However, when I introduce new rows in the PopUp form, I also realized that the calculated Textbox in frmSubMilestones is not updated when I close the frmPersonDays PopUp. I have to close the PopUp form and move the cursor in the Main form (frmProjects) to refresh its associated milestones.
I have created a form that is based on a table.
The fields in the form are currently pull downs or manual enrty fields (all based on the columns of the table... "Bound" I think you call it).
I also have fields that I manually brought into my form as text boxes that I am using to populate a series of numbers. I have named them P1, P2, P3 etc. They are unbound fields.
I have a field that I am totalling the unbound fields. It is a column in my table. At one point the control source name was the table coulmn name.
I have changed the control source to read =[p1]+[p2]+[p3]+ etc.
The total does show up in the field on my form
HOWEVER.... When I save the data the total number does not get brought into the table. All of the other fields do.
If I have a Form that is used to populate a Table can I make one field entry dependent upon another?
For example, if I have a field for Discipline (with values such as Mechanical, Electrical, Piping) and another field for Equipment Type (with values such as Motor, Gearbox, etc) can I set up the Form so that when I select Electrical for the Discipline, only electrical items (such as Motor) are available to select for the Equipment Type field?
Using the table below as an example, if I select Electrical for the Discipline field in my Form, when I go to select a value for the Equipment Type field the drop down shows only Motor and Fan?
Discipline
Equipment Type
Electrical
Motor
Electrical
Fan
Mechanical
Gearbox
Mechanical
Pump
Piping
DN 150 Pipe
Or, better still, can I set it up so that if I select Motor for the Equipment Type field, the Discipline field will automatically be populated with Electrical?
I am trying to run a make table query that involves using a linked table in Ms from a SharePoint list. However, a few columns I wish to include in my Make table query are calculated columns from the SharePoint list.
The make table query will not run saying "Calculated columns are not allowed in SELECT INTO statements".
Can any think of a solution or a potential work around to this?
I need to create a table with these columns in it.
I have a text box and currently this is my control source
="Testing " & [test]/3
test = 1000 so my text box reports:
Testing 333.333333333333
Is there a way to make it into a form like $333.33...Also is there a way to make [test]/3 come out in a money text form? like "Three hundred thirty three dollars and thirty three cents.