Continuous Form, Ordinal Index By Function, #Error
Jun 16, 2005
I want the recordnumber displayed on a continuous form. The code below works with the command button, but not afterinsert.
Here's the code: (function GetPosition() is the control source)
Private Sub Command28_Click()
numbers
End Sub
Private Sub Form_AfterInsert()
numbers
End Sub
Private Function getPosition() As String
If Me.RecordsetClone.RecordCount > counter Then
counter = counter + 1
getPosition = "#" & counter
Else
getPosition = "#new"
End If
End Function
Private Sub numbers()
counter = 0
Me.Requery
End Sub
So it should do something like this:
#1 [ctrl 1]
#2 [ctrl 1]
#3 [ctrl 1]
#new [ctrl 1]
But called from after insert only the records on the screen get updated unless I scroll up, so simplified it looks like this:
----------------- top of subform after scrolling up
#3 [ctrl 1]
----------------- top of subform before scrolling up
#1 [ctrl 1]
#2 [ctrl 1]
#new [ctrl 1]
Where else can I put it or how can I modify the code to make it work?
Aidan
I created a subforum with an updateable query, and this works. I can view the results of the query, modify them and add new records.
However I shortly realized that once I deleted a record (either by some VBA code, or by SQL) that I could not re-add the same record through the subform.
I've made sure that the record is deleted, so there is no duplication of the key. I've made sure that Index is Duplicates OK, so there is no duplication issues there.
Is there a way to format the day of the month as an ordinal number? For example instead of formatting the day as January 1, I would like to format the day as the 1st, 2nd, 3rd etc. day of the month. I need to do this in order to match the format of a contract that we want to use as a mail merge.
Using an Access 2003 format database, opening in Access 2007...When I try to open my database I get two errors and it will not open.ID is not an index in this table.ParentId is not an index in this table.
I get the error when it opens with autoexec and when I bypass autoexec. I have a master copy of the database that I tried to link to the first database to import tables but I still get the error.
I have a Composite Index to prevent duplicates I get the error message. How can I trap this?
I resolved it with this PHP Code:
'Trap Error. Dim DataErr As Integer Dim Response As Integer Dim Message As String If DataErr = 3022 Then 'Duplicate value entered Response = acDataErrContinue End If
I was using the following code on a field (ItemCode) that was indexed to prevent duplication of records. The intent is that the user will get an error message that a duplicate exists before they enter all the data for the record and get the built in error message that Access 2003 provides when an index violation has occurred.
Code: Private Sub ItemCode_BeforeUpdate(Cancel As Integer) Dim Answer As Variant Answer = DLookup("[ItemCode]", "tblQuestions", "[ItemCode] = '" & Me.ItemCode & "'") If Not IsNull(Answer) Then MsgBox "Item Code already exists" & vbCrLf & "Please enter unique Item Code.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate" Cancel = True Me.ItemCode.Undo Else: End If End Sub
Now, the index for this is based on two fields (ItemCode and Question Group). I would like to display the same message before update but don't know how to include the second field in the syntax.
I am trying to create a one-to-many relationship between these two tables. I want to be able to access the 3 fields on the [Processors] table within reports based on [AllItems]. [AllItems] is a listing of account activity where the [AccountNumber] repeats. I have every field set as the "Primary Key" on [AllItems] as that is the only way to avoid importing duplicate data. I am getting the error: "no unique index found for the referenced field of the primary table"
I have an IIF function specified on a continuous sub-form as below - basically it checks to see if the value of two text boxes is equal, and if they are it displays 'And', if not it displays the value of the column EntitlementName. I'm trying to work around grouping not working in continuous forms.
When I view the form directly it works no problems but when I view the form as a sub-form instead of showing the correct output it shows #Name? Both the text boxes I refer to have the correct values in them.
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
I am struggling trying to execute a function inside a Form_current event to display some stats.
The Function is this:
Code: Function FlightsByAircraft(Aircraft As Long) As Long Dim rst As DAO.Recordset Dim dbs As DAO.Database Dim str As String str = "SELECT * FROM tblFlights WHERE AircraftID = " & Aircraft
[Code] ....
The code for the Form_Current event is this:
Private Sub Form_Current() txtStats1 = FlightsByAircraft(Me.AircraftID)
Very simple. Well, the problem is when I move to a new record, a error message comes up: "Run-time error '94' - Invalid use of Null". It is because the AircraftID is not populated at that time. I tried to insert in the function code something like that:
Code: If IsNull(Aircraft) then exit function else .... (the DAO.Recordset code)
What I am trying to do is put a subform within a subform. The trouble is I want both forms to be continuous forms.
when I put the subform within a subform, access comes up with the following message:
************************************************** ******************** A form with a subform object cannot have its DefaultView set to Continuous forms.
You tried to add a subform to a form in design view. MSAcess will reset the property to Single Form. ************************************************** *********************
which it does my higher level form becomes a form only showing single records at a time whilst the subform is a continuous form.
I got this "AOIndex is not an index in this table" error with Access 2000 after replicating a database and storing it in the same network folder. I am not able to open my database now, i am afraid 2days work on my forms is lost. I usually have backup, but the last backup i have is 2 days ago i have made alot of changes to 2 FORMS after that :(
Please anybody have suggestions on how to retrive my forms??? I am really concerned about the forms. I have backup for data and for the reports. Please help me?
How can I refer to a form by using its index number? Also how can I retrieve an index number of a form?
(I'm opening multiple instances of the same form and read that the only way to refer to those instances is by using their index number. I found some solutions that use hWnd as well but I can't get any further support on it as far as referring to a form by using the window handle so I'm trying to capture and then use the index number)
I don't get it!!!! I use it all the time. <=Date() should get all rows with no future dates...but I get this error on some of my queries...I've never got this error before... :mad: :mad: :mad: :mad:
I have used the Trim function in a query and am getting an error. At first I was concatanating several fields but have cut it back to the most basic form but still receive the error. The error says that the trim expression failed. Here is the code I used:
Hi - I am using the replace function in a query expression to remove spaces in a postcode field:Replace([POSTCODE]," ","")) However, this returns an error if the Postcode is left blank - as I am trying to match the postcodes from 2 queries the error is giving a type mismatch.(I presume its this anyway)I am thinking I need to use an Iff function deal with the instance of no postcode(i.e. NULL) but I can't get it to work.IIf([POSTCODE]=NULL,"No Code",Replace([POSTCODE]," ",""))Would be grateful if anyone could help me out.ThanksRuss
I made a function to calculate the difference in hours between two times.
Function TimeDiff(Time1 As String, Time2 As String) As Double 'This function is used to calculate the difference in hours between two times. 'It is capable of handling a finish time that is after midnight.
TimeDiff = 0
If Not IsNull(Time1) And Not IsNull(Time2) Then
If CDate(Time1) > CDate(Time2) Then TimeDiff = ((CDate(Time2) - CDate(Time1)) + 1) * 24 Else TimeDiff = (CDate(Time2) - CDate(Time1)) * 24 End If
End If
End Function I have a query where there are 2 sets of times in fields (Start1, Finish1, Start2, Finish2). When I add a calculated column with the following expression: Expr1: TimeDiff(Start1,Finish1) + TimeDiff(Start2,Finish2) it calculates in the query correctly.
However, when I made a new function that incorporated this function, it gave me the #error result in the calculated field. Function OrdinaryHours(Status As String, Start1 As String, Finish1 As String, Optional Start2 As String, Optional Finish2 As String) As Double
'Initialise OrdinaryHours = 0
'If employee is NOT a casual If Not Status = "CT" Then
If Not IsMissing(Start2) And Not IsMissing(Finish2) Then OrdinaryHours = TimeDiff(Start1, Finish1) + TimeDiff(Start2, Finish2) Else OrdinaryHours = TimeDiff(Start1, Finish1) End If
End If
End Function Does anyone know why this would #Error. I have the TimeDiff function initialising to = 0 before doing anything...I am stumped why it returns #Error when it should return 0 in the case of something going wrong.
I'm running an update query and I get this error - Function Sequence Error. (#0)
I have an excel spreadsheet that I imported into a table. I am trying to update the part table with date from the spreadsheet. The part id is unique and thats how the tables are joined. I only need to update 5 fields. The table I'm trying to update is a linked table.
I can't figure out what I need to do to make the necessary updates.
Hi, i know ive asked this in another forum, but i just read a thread on a query function which may be able to deal with this so thought id post it here:
the function i just saw:- Expr1: IIf([Sold],="","0",[Sold]) this does nothing to help my problem though so any ideas?
i have three fields; [boughtin], [sold] and [instock]. [boughtin] comes from one query and [sold] from another.
[instock] = [boughtin] - [sold]
because [sold] only has a value once an invoice has been created for that item, it means that if you try to view an item that hasnt been sold yet, you see how many have been bought in, but no sold value and then because of this, no stock value. (you just get an '#error' value in the stock box.
basically what i need is this: if both [boughtin] and [sold] are present, then the calculation is done and a [stock] value is shown. if only [boughtit] is present then the stock value simply shows the [boughtin] value.
[boughtin] is located on one subform (frm_stocksub) and [sold] is one another (frm_stocksubsub).
i need a code to put into the after update event of the main combo on the main form so that it detects this and then displays the approriate value once both queries have run.
a simple idea of what i want is: If [Sold] has no value Then [InStock] = [BoughtIn] Else [InStock]=[BoughtIn]-[Sold] End If
Obviously there is far more detail in terms of locating the particular text boxes, but i hope it gives you an idea of what i want.
I am working on a marketing database. I have two tables that are in use called tblListInfo and tblResults.
In tblListInfo the fields I am using are DropDate, ListName and Marketcode. In tblResults the fields I am using are JoinDate and Marketcode.
I am attempting to write a query that the end result will display the number of records within the 1st week of the promotions drop date. For every combination where the ListName value AND the DropDate value are unique, I want to count the number of records in tblResults that have JoinDate BETWEEN DropDate AND DropDate + 6.
I tried writing the code for this in pieces starting with:
SELECT DISTINCT tblListInfo.[ListName], tblListInfo.[DropDate] FROM tblResults INNER JOIN tblListInfo ON tblResults.[Marketcode] = tblListInfo.[Marketcode]
This gave me my first ideal part of the output I was seeking. I listing of all the possible marketing lists used with duplicate names occuring only when there were different drop dates. for example:
Listname Drop Date List - 1 1/1/08 List - 1 1/15/08 List - 2 1/1/08 List - 2 2/5/08
From there I wanted to modify the process to include a third field that included the # of records in tblResults that have the JoinDate field BETWEEN tblListInfo.[Drop Date] AND (tblListInfo.[Drop Date]+6). The ideal output would be like this:
Listname Drop Date Week 1 List - 1 1/1/08 15 List - 1 1/15/08 8 List - 2 1/1/08 32 List - 2 2/5/08 12
My attempt at getting to this result is below:
SELECT DISTINCT tblListInfo.[drop date], tblListInfo.[ListName], count(tblResults.[join date]) AS [Week 1] FROM tblResults INNER JOIN tblListInfo ON tblResults.[MarketCode] = tblListInfo.[MarketCode] WHERE tblResults.[Join Date] BETWEEN tblListInfo.[drop date] AND (tblListInfo.[drop date]+6)
I receive an error at this point saying: "You tried to execute a query that does not include the specified expression 'ListName' as part of an aggregate function."
Can anyone offer suggestions/corrections to my approach and logic?
Hello I keep getting the error after I copied a form and saved under a different Name ++++++++++++++++++++ A problem occured while Microsoft Access was communicating with OLE Server or ActiveX control.
There may have been an error evaluating the function , event or Macro
+++++++++++++++++++++++++ Strangely, I keep getting this error when loading and clicking on a control in the ORIGINAL form while the copy seems to work without errors.
I have deleted some tex box controls from the first form and added a few new ones in the copy.
I have tried a copy of the database by deleting the New form, but still get the same error.
I have run DEBUG on both form codes and everything seems fine.
I have checked the refrences and find none missing.
Is there any way to resolve this issue. Or am I doing something fundamentally wrong by copying and modifying the form ?
I have used access to create a database for my web site which I use ASP to connect. I've written a function in Access which takes a product description and shortens it down...
Public Function GetShortDesc(strInput As String)
Dim x As Integer Dim tempstr As String Dim checkstr As String x = 100
tempstr = Left(strInput, x) If Right(tempstr, 1) = " " Then GoTo stringfound Else Do Until checkstr = " " x = x + 1 tempstr = Left(strInput, x) checkstr = Right(tempstr, 1) Loop End If
stringfound:
GetShortDesc = Left(strInput, x - 1)
End Function
And I have used this function in a query which works fine in Access, but when I go to my ASP page and try to return the field to my page I am getting an error...
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14) [Microsoft][ODBC Microsoft Access Driver] Undefined function 'GetShortDesc' in expression.
It looks like the ASP page isn't accepting my function, Can anybody help???