Forms :: Records Are Sequenced Incorrectly On A Form
Oct 17, 2014
I have created several tables, and also created forms associated with each of them. In every case, the primary key is the first column of the table, and it is autonumbered starting from 1. Every table is sorted by that first column.
In most cases, when I bring up the form that is associated with the table in Form View, as I sequence through the records using the buttons at the bottom of the screen, the records sequence in the order of the autonumbered primary key in the order you would expect (1,2,3,etc)
However, on one form, when I do the same thing, the records sequence through in a different order. When it shows "1 of 21" at the bottom, it is displaying record number 8. "2 of 21" displays record number 13, and so on.
I have doublechecked to see that the associated table is actually sorted properly and it is. I've gone to the Property Sheet in Design view, but I don't see anything that would control the sequence order.
I am using a form with a combo box (FieldExpenses) that lists from a query row source (SELECT statement). Whether entering a new record or editing an existing record on the form, the selection in the combo box populates the field in the underlying table (TableTransactions) with the row ID (ex: 105), not the desired field (ie: Telephone).
I tried using a split form and the table in the form shows the field correctly. If I open the underlying table separately, the field displays the row ID. It's not working as I wish.
I have a report that prints blank serialized forms and I basically need a query to generate a field where if I request 4 sheets to be printed the list generated would look like this:
ClaimNo 1 2 3 4
It seems like this would be simple, but I'm a noob.
SELECT Sum(TBL_MeasuresInstalled.FundingValue) AS ATP_Value FROM TBL_MeasuresInstalled GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
and it returns nothing when there are no records that match the HAVING clause - I want it to return 0 instead
I have searched the forum and people having similar issues have been advised to use the NZ function so I tried
SELECT NZ(Sum(TBL_MeasuresInstalled.FundingValue) ) AS ATP_Value FROM TBL_MeasuresInstalled GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
and
SELECT NZ(Sum(TBL_MeasuresInstalled.FundingValue) , 0) AS ATP_Value FROM TBL_MeasuresInstalled GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
I have been staring at this for hours. If anyone can see what I have done wrong I would be so grateful.
In query Q_CTPayDeadline_1 Record ID_Tasks = 194 Field [Stage1B] SHOULD (I believe) evaluate to date 2010-01-01, not 2004-03-31, the former being greater than the latter.
The criteria for field Stage1B is Stage1B: IIF([NzCease]>[PriorEnd],[NzCease],[PriorEnd]) The values of NzCease and PriorEnd are displayed to the right of Stage1B, for quick reference. As you can see, for recird ID_Tasks = 194, the values shown are NzCease = 2010-01-01 PriorEnd = 2004-03-31 BUT Stage1B = 2004-03-31 ie it treats the IIF() function as returning false, when it should be true.
One shows my form with the Transporters Subform with 3 entries, and 1 entry.The three line items that say "Transporter" are in one subform. I used this code
Code: Private Sub Form_Current() If Me.RecordsetClone.RecordCount >= 3 Then Me.AllowAdditions = False End If End Sub
to limit the number of records I can add to 3 or less.My issue is that I lost the blank text box that allows you to add another record. So, if I only have one Transporter listed, there's no box to let me add a second or third.I have the following properties for the Transporters Subform set to "Yes":
Data Entry Allow Additions Allow Deletions Allow Edits Allow Filters
Hello, I'm trying to sum values in a table and the result I'm getting is incorrect.
I have a simple table called Test with one row called Number which is Double. I have 4 values in Number field. They are: -60000 26.6 2661 62849
When I run a query to sum those fields: SELECT Sum(Number) As Sum FROM Test; I get the following result: 5536.60000000001
Interestingly if I change the last number in my table from 62849 to 62848 I get this result: 5535.6
The problem seems to be with summing positive and negative numbers together. In my case, if the sum of positive numbers goes over 65535, the resulting sum is incorrect (adding 0.00000000001 to the result).
Does anybody know why this is happening and if there is a way around this? The actual tables that I work with have a large numbers that go well over 65K so this is a problem for me. I couldn't find anything on google about this.
I have a form which needs update ever month. When the form is opened, the end user can see the old records and data, and also a new record is added for any new data. I want to protect the old saved data and the user can only add, edit, or delete the new data in the newly added record. The problem is once a user adds the new data and moves to another record or another form, then he/she cannot edit or change the new data in case if there is any mistake or need to change something after couple of minutes.
I changed the Form Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" many different times and situations to solve this problem but with no success. I tried the following with NO success too: One of the Fields of the Record is (Month). In the Data Properties, I set a Default Value for this field as(December 2014) for instance. I set the Data Properties "Allow Additions", "Allow Edits", and "Allow Deletions" to (Yes). Then I put the following code in the Form's Current Event:
Private Sub Form_Current() If Me.month.Value = "December 2014" Then Me.AllowAdditions = True Me.AllowEdits = True Me.AllowDeletions = True
I am getting this error "This expression is typed incorrectly, or is too complex to be evaluated"
For this query: SELECT first([TblProp].[Name]) AS [SName], First([TblProp].[CommentDate]) AS DateCommented, First([TblProp].[No]) AS BNum, First([TblProp].[Indication]) AS Ind, First([TblProp].[PropSubmitted]) AS DateSub, First([TblProp].[Contact]) AS PrimCon, First([TblProp].[Prepared]) AS PrepName, First([TblProp].[Comment]) AS Comment,First([TblProp].[Value]) AS ValueNew, First([TblProp].[Rating]) AS Prob FROM TblProp GROUP BY [TblProp].[RFPNo];
This query had been working fine for a good long time, but suddenly it starts throwing up this message. I haven't changed anything at all with this query. Its very puzzling. I went through each of the fields and its the comment field that is causing the problem.
Would anyone have any ideas on why this might be happening?
I have a standard form with header, detail, and footer sections. The detail section has a range of calculated boxes and the footer section also has a range of calculated control boxes.
There is a button on the form, which runs the following code: DoCmd.OutputTo acOutputForm, "Divisions", acFormatXLS, "C: ester.xls", False
This works as far as exporting the Detail section of data. However, as soon as it trys to export the footer section, it just appends them onto the end of the rows. For example, the report lists: ABCD 10 20 DEFG 20 30 Totals 30 50
The excel spreadsheet after export lists Controlbox name Controlbox name Controlbox name cbn cbn ABCD 10 20 30 50 DEFG 20 30 30 50
Is there a way to: (a) correct this in the export; (b) format the spreadsheet; (c) alternative, e.g. drag the data in from a sppreadsheet ?
I have a table "StoreInfo" that stores the store number, store address, etc as text fields. I then have created a query gathering this data, specifically store number and address as separate fields. When I run the query, the store number and address are correct. When I use that same query in a report, I have one specific store where all data appears on the report correctly except the store number. The store number is "007" but appears on the report as "137". I was able to correct the problem by changing the store number to eliminate the leading 0's.
Since the field is a text field, I just assumed I would not have any sort of problem with the leading 0's. Once I changed the store number to 107, the problem disappeared.
I have 2 tables one Access, one FoxPro both containing similar information. The idea of my query is to compare both tables and update the Access table if the value in the FoxPro table has changed. This works fine for a number of similar queries that I have. However, intermittently, this query will see two values as different when they are exactly the same and replace the value in the Access table with a seemingly random value. I know the "Random" values are from other records within the table but cannot see any link between the values.
I have recently started learning to use Access and all is going very well except for the date. As can be seen from the images below I have set up my date function correctly in Control Panel and everywhere I use a date it is displayed correctly. In Access however numbers are added which I cannot figure where they are coming from and don't want them there. In the second image you can see what the various Format options look like. When long format is used in a Form or Report it displays those additional figure. I cannot get 2015 to display but get 15316 or similar instead.
to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I did a search and it looks like date prompts cause this error sometimes. The message pops up when running from the Switchboard to generate a report. When you hit the button, you are prompted for the Start Date and End Date, but then this message appears.
Running the query directly, not using the Switchboard or Report to generate the data, it runs fine, still using the two prompts.
Any idea on what I should look at to resolve this issue so users can run the report from the switchboard? Let me know if I need to post the sql or something.
I have some data I need to clean. It is in an address field the majority of the data is fine but there are some records with words that have been spelt with numbers:
I have a bound continuous tabular form,However, based on data content in one field of a record, I want a checkbox in the same record enabled, so the user can check it if necessary. I have created a record set using the form as shown below, and I am looping through each record. To show that my code is referring to the field with required data content, I display it as a message box and it works, yet my checkbox does not enable.
I have the code in the form_load event, however, for testing purposes I have it behind a button.If I am seeing this properly, the code behind the button enables the checkbox for ALL records once the criteria in the required field is true, and based on the last record, which has no data content, it disables the checkbox in ALL records. I also have the PK ID for each record hidden in the form. Can I utilize that to target the checkbox of each individual record??
Form Detail -Form does not allow additions or deletions. Edits allowed -All fields are disabled and locked -I only want the check box to unlock if data is found in the "RequiredField" as referred to below. I have also tried if not isNull(requiredfield.value) then -enable checkbox, which yields the same results
Here is my code
Code:
Dim rstMyForm As DAO.Recordset Set rstMyForm = Forms!MyForm.Form.Recordset rstMyForm.MoveFirst Do While Not rstMyForm.EOF If Not RequiredField.Value = "" Then
The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.
The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.
We have a database with one main data entry which stores records in a seperate table. There are several thousand records keyed in using this form. Therefore, alongside the entry we have a search form which allow the user to search for a specific record and display it in the main if he/she needs to edit one.
We have received a request from users for a functionality which allows users to browse all records, using the main form layout, that they have keyed in. I guess what I am asking is: Is there a way to limit the records visible in a form using field on that same form as a limitation (ie. the users name - which is a required key-in)?
My client wants to be able to have a form open with a record, then open the same form with a different record. In all my experience I have not had this request or seen it done. I assume I could have a different form to open the second record. How to confirm my assumption?
The situation is they have a sales lead open and they get a call and need to open another sales lead without closing the other one.
I am building a Form based on 3 Tables. Each with primary keys, although two tables do not relate to one another, and I am using one table to be the link within both of them, which has both of the primary key fields within it.
The issue is that I am building this form and in the control source I am building this query to pull the information for the form, which I was hoping will allow me to add new records (Not Working ). I am also attempting to accomplish that when I integrate a field from one of these tables into the form (the table that does not relate to it at all, other than through the linked table) that it will show the fields relating primary key in a transparent text box. This field I am integrating will be disabled for editing (like transparent), but will show up when the corresponding field (primary key) populates with the appropriate number.
The Fields in the pictures on the form that are dimmed are the fields that will not be enabled, but once I do this it does not allow me to enter New Records .Here is the SQL:
SELECT TblSupplierTests.*, TblCreamReceiptsScaleWeights.LoadID, TblLOLCreamTests.BFTestForSettle, TblLOLCreamTests.SNFTestForSettle, TblCreamReceiptsScaleWeights.RecievedDate, TblCreamReceiptsScaleWeights.SupplierLBS AS SupplierLBS_TblCreamReceiptsScaleWeights FROM TblLOLCreamTests RIGHT JOIN (TblSupplierTests LEFT JOIN TblCreamReceiptsScaleWeights ON TblSupplierTests.BOLID = TblCreamReceiptsScaleWeights.BillOfLadingID) ON TblLOLCreamTests.SO = TblCreamReceiptsScaleWeights.LoadID;
I want to create a form in MS access for General Journal input for an accounting software. Problem is that my client required a form in which he can input as many Debits and/or credits as he likes, for one transaction on one form. For example it may be 2 debits and 4 Credits in one transaction. Its a simple one table entry with fields; TrNo (Transaction Number), Date, Type (Debit or Credit), Amount, and Narration (for details of transaction). Out of these field TrNo, Date & Narration will be same for all debits & credits in one transaction.
Once I enter records in my form and move to a new record, they won't show up in the form. For some reason there are a few records showing in the form but not all. Unsure what has happened or what I need to do to get the records showing.
If I open a the form called frmhome, I can enter information search on hours completed and even enter new towns. The table that the information gets entered into is called Crisis_support_workers. If you open this table, you will see a lot of information that has been entered. However, if you open the form that is meant to put info into that table, there is only 7 records and most of them are blank records. The form where info is entered is called Crisis_support_workers v3.
I have a form where users can look through different records as well as add a new record through a control button. When a new record is added, all the relevant data should end up in the corresponding back end tables.
The problem I am having is that some of the new records I am adding work perfectly (sync with the tables, etc). Other records, however, are showing up in the tables but not in the actual form. For example, when I attempt to search in the form for the record name, nothing comes up.
The Datasheet View is set to No, so it's definitely not that. I'm not really sure where else to look - the new records appear in tables and the report I have linked to the form, they just don't all show up in the actual form!
Basically I have a table where freelancers have multiple records for each module they teach. What I am trying to do is show all records for each person onto the same form, but anything I have followed still only brings up the first record.
This information is all on the same table, so would this be easier to be on separate tables to then create relationships?
