Using OrderBy Gives Error At Reopening Form.

Nov 15, 2006

I have a form where the recordsource is a stored procedure.

This forms shows a list. When the user clicks on a column title, the column is sorted by using OrderBy in VBA:

f.i.:

Private Sub lblCode_Click()

Me.OrderBy = "strCode"

End Sub

As a result, in the properties of the form, for the property Order By, the fieldname is filled in (f.i. strCode).

This works fine, but when the form is closed, access saves the property Order By.

Next time the form is opened, it gives an error: The recordsource <storedprocedurename> specified on this form does not exist.

When I remove the fieldname in the Order By property of the form, the form opens without an error.

I have tried to clear the Order By property by using Me.OrderBy = "" in the Close event of the form, but this does not work.

Any solution ?

View Replies


ADVERTISEMENT

Forms :: Form After Close And Reopening Not Picking Up Fields

Sep 11, 2013

I have cleared the data out of the database and started testing it to check its usability.I entered in a full record, and flicked back and forth between the records, and everything was looking good.

Then I closed the form, and re-opened it and my record was no longer populating the form fields.I've literally changed nothing bar one field that was changed from number to text.

View 5 Replies View Related

Modules & VBA :: OrderBy When Opening A Form

Nov 29, 2013

I'm trying to have a form sorted in a particular order when it's opened.

When using the "order by" option in the properties sheet, it is sorted the way I want the first time I open the form.

However, if I change the order afterwards while using the form (for instance, by right clicking and selecting another sort order), well the next time the form is opened, it is sorted with this new sort order, and not the default one.

So I tried to add the following code :

Code:
Private Sub Form_Open()
Me.OrderBy "employee_name ASC"
Me.OrderByOn = True
End Sub

Which failed epically.

View 6 Replies View Related

Transfering RecordSource/OrderBy Of Form To Report

Oct 17, 2006

I have a subform within a form that displays filtered data based on dynamic selection criteria which is assigned to the RecordSource/OrderBy of the subform. On this form, I have a "Print Results" button -- OnClick, I would like the RecordSource/OrderBy of the subform to be copied to the Report, then run the report. What I have that doesn't work is:

Reports!rpt_frmTaskMain.RecordSource = Forms!frmTaskMain.frmTaskSub1.RecordSource
Reports!rpt_frmTaskMain.OrderBy = Forms!frmTaskMain.frm.TaskSub1.OrderBy

On execution I get error: "Object doesn't support this property or method"

I am thinking this can be done, and likely have the references wrong. Help?

View 1 Replies View Related

Forms :: Can't Clear Filter Or Orderby Permanently On Form

May 14, 2013

I have a tabular form that is based on a select statement (Query) in the source property. It filters & sorts from this select statement. permanently

I have just added a
Me.Filter = "[Form1].[InDate] = #" & MyValue & "# "
Me.OrderBy = "Indate DESC"

This all works fine, filters & sorts on the existing results.However this new filter & sort seems to remain no matter what I do.I have used every variation one me.filter ="" with Me.FilterOn = True, False & every combination.

Me.Filter="" & Me.FilterOn = False does initially seem to clear the lsit to show all records but as soon as type anything that filters the Form on the original, existing underlying select statement the extra Filter Property & Sort properties are occupied with the last filter/sort.

I have manually deleted any remaining Filter Or Sort properties direct from design view, saved, compacted & saved again.Once I use the new me.filter it will always returned as soon an any filter on the form is applied.I definately am not setting it again anywhere. Is there something better than me.filter ="", seen clearallfilters mentioned but not sure if this would apply just to the current form but reluctant to use a blanket clear as I do have quite a lot of other things going on.

Not sure when but many months (If not years) ago my form source stopped being a query and started using the query (sql) direct from the source property.I have left it this way as not sure if any consequence of moving back but don't remember how or how it happened. What are the pros cons of using a separate query.

View 4 Replies View Related

Modules & VBA :: Form Filter And OrderBy Pass To Report

Aug 16, 2014

I am able to filter a data on a continuous form using drop downs and then the following code attached to a cmdbutton to create a report of the filtered data.

Code:
Private Sub Command30_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "rptconveyorerrors", acViewReport, , strWhere
End Sub

On the same form where I filter the data i can sort it by clicking the headings aswell, however when i generate the report using the above VBA it doesn't take the sort with it and just generates it without the sort.

I am using the following VBA to sort my form

Code:
Me.OrderByOn = True
If Me.OrderBy = "[empname] DESC" Then
Me.OrderBy = "[empname] ASC"
Else
Me.OrderBy = "[empname] DESC"
End If
Me.Refresh

I thought it may be possible to use the following sort of VBA to pass the sort however i cant get it to work:

Code:
Private Sub Command30_Click()
Dim strOrder As String
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then strWhere = Me.Filter
If Me.OrderByOn Then strOrder=Me.OrderByOn

View 1 Replies View Related

How To Avoid Duplication Without Closing And Reopening FrmPDMonitor

May 7, 2014

Once you open the db. open up the frmQuoteLog and the frmPDMontior. You will notice one record is displayed in the frmPDMonitor (Justin Ryan). This is correct.Now to test out the issue leave open the frmPDMonitor and frmQuoteLog at the same time. Go to frmQuotLog, go to the first record, make the "Sales Coordinator" = "Chris April", then check the box that says "Display on Product Design Monitor", then press the "Save Changes" button.

Then look at the frmPDMonitor and you will notice the new record is now displayed on the form. This is correct as well.Leaving open both forms, go to frmQuoteLog and uncheck the box you previous checked on the first record, then press "Save Changes".

Then look again at the frmPDMontior and notice that the previous record is gone but the remaining record is now duplicated.Only when I close the frmPDMonitor and reopen it, does the duplication go away.How can I avoid this duplication without closing and reopening the frmPDMonitor? The purpose of this form is to be ran 24/7.

View 14 Replies View Related

Modules & VBA :: How To Avoid Duplication Without Closing And Reopening FrmPDMonitor

May 7, 2014

Once you open the db. open up the frmQuoteLog and the frmPDMontior. You will notice one record is displayed in the frmPDMonitor (Justin Ryan). This is correct.Now to test out the issue leave open the frmPDMonitor and frmQuoteLog at the same time. Go to frmQuotLog, go to the first record, make the "Sales Coordinator" = "Chris April", then check the box that says "Display on Product Design Monitor", then press the "Save Changes" button.Then look at the frmPDMonitor and you will notice the new record is now displayed on the form. This is correct as well.

Leaving open both forms, go to frmQuoteLog and uncheck the box you previous checked on the first record, then press "Save Changes".Then look again at the frmPDMontior and notice that the previous record is gone but the remaining record is now duplicated.

Only when I close the frmPDMonitor and reopen it, does the duplication go away.How can I avoid this duplication without closing and reopening the frmPDMonitor? The purpose of this form is to be ran 24/7.

View 6 Replies View Related

Modules & VBA :: Network Crash - Any Way To Reconnect Objects Without Closing And Reopening Database

Oct 23, 2013

I have a database that runs a macro/report every 2 hours, however our network has been up and down lately. This causes the macro to fail when trying to log into the system because the table that is storing your username is no longer in the database. When I check, all tables, linked tables, forms, reports, macros and modules are all gone. I find the only way to get them back is to close the database and reopen.

What I want is a way to reconnect the objects without closing and reopening.

Is there a way to do this?

View 1 Replies View Related

Orderby

Apr 26, 2006

Hallo everyone,

I have a table with one of its column value as "B" or "D" or "E".

Now i am in need to display the rows using the select statement with the following conditions.

1. First display the rows with value "D"
2. then display the rows with value "E"
3. then display the rows with value "B"

Is it possible to specify the above condition in a orderby clause.

thanks,

aravind.s

View 1 Replies View Related

OrderBy With Wildcards

Oct 13, 2006

I am trying to get the OrderBy to recognize wildcards (or to that effect). A scenario would be I would have more than one name in a field, EX: Name2, 4, 6. How do I use wildcards in the OrderBy and they are recognized.

ORDER BY (Table.Field)="Name1", (Table.Field)="Name2", (Table.Field)="Name3", (Table.Field)="Name4", (Table.Field)="Name5", (Table.Field)="Name6", (Table.Field)="Name7", (Table.Field)="Name8";

View 2 Replies View Related

Listbox OrderBy

Jun 20, 2006

Hi,
I have a list box based on a quite a complex query. I would like give the user the option to dynamically sort the list box.

How can I refer to my listbox's SQL rowsource and add a sort order using VB?
i.e.
Private Sub CmdButtonSortByName_Click
Dim strSQL as String
strSQL = me.listbox.rowsource & "ORDER BY FieldName;"
me.listbox.rowsource = strSQL

I am aware of how I could do this if my SQL was in VB i.e.:

Dim strSQL as String
Dim strSortOrder as String
Dim optSort as Integer

Select Case optSort.Value
Case 1
strSortOrder = "ORDER BY LastName;"
Case 2
strSortOrder = "ORDER BY FirstName;"
End Select

strSQL = "SELECT FirstName, LastName FROM TblName " & strSortOrder
Me.ListBox.Rowsource = strSQL


....but my query is far more complex than this and I dont want to have to keep calling the code each time I need to requery my form.

Thanks
BF

View 14 Replies View Related

Orderby Syntax

Nov 2, 2004

I want to basically perform a "Sort by ascending" Function.

I presume I have to grab the focus of the Item I want to order by.

So :

me.(Field).Setfocus

But then what is the code for running the Sort by ascending/Descending?


My data is currently in a list of text boxes. When I normally 'Sort by ascending' the function works fine.. so I just need to activate that....

View 4 Replies View Related

OrderBy Problem

Apr 15, 2005

In my db I have a report that I would like to sort by different fields. I tried coding this using option buttons, a command button, and the "Case" statement. While putting in the OrderBy portion of the code I keep getting errors in relation to the syntax. I'm not sure how long I worked with it and got absolutely nowhere. Can someone clue me in please? Here's my code:

Private Sub Command14_Click()
Select Case Me!Frame5.Value
Case 1
'Sort by Military Occupational Specialty (MOS) ascending
DoCmd.OpenReport ("Report1")
OrderBy MOS

Case 2
'Sort by Military Occupational Specialty (MOS) descending
DoCmd.OpenReport ("Report1")
OrderBy MOS, DESC

Case Else
MsgBox ("Please select an option before attempting to open the report.")

End Select
End Sub

View 6 Replies View Related

Weird OrderBy Problem

Feb 21, 2006

Hi all,
Found a weird problem when editing records in continuous form view (and datasheet view) when a particular memo field is being used by OrderBy to sort the data. When i have just created a new record and tab to the next record, the record i was just in jumps to the top of the records, but in its place is an exact copy of the record above. Very confusing, and looks like a redraw bug but it happens on all PCs.

What happens is ... (letters relate to records going down the screen)

A
B
C
D ....new record being entered then <TAB> and ...

D ....has jumped up here
A
B
C
C ....duplicate of C - but duplicate record never created in the table
E ....new record

When i quit the form and come back in, everything is ok

As far as i know, OrderBy only takes effect if you refresh the form (which i'm not doing). It works fine if i take off OrderBy, or if i use another field with OrderBy. So is there some problem using OrderBy with a memo field, and how is this making my records jump about?

help!

Pete

View 1 Replies View Related

Modules & VBA :: Error Msg On Form With Form Closing?

Nov 13, 2014

I got an error msg on form with closing. I have a main form (SalesReturnOrDamaged) and subform (SalesReturnOrDamagedSubform).

i have used a VBA code on main form Close Command button as per shown in Code Tag. But that is not working and shown an error msg as per screenshot. how to change that VBA code for empty form close without save the record?

Code:
Private Sub Close_Click()
On Error GoTo Err_Close_Click
If Forms![SalesReturnOrDamaged]![SalesReturnOrDamagedSubform].Form![ItemCode].Value = "" Then
Forms![SalesReturnOrDamaged]![SalesReturnOrDamagedSubform].Form![Product].Value = ""
Forms![SalesReturnOrDamaged]![SalesReturnOrDamagedSubform].Form![MRP].Value = 0 Or ""

[code]......

View 1 Replies View Related

Form Error!!!!!!

Nov 1, 2006

Hi, i have a problem with a form, i don't know how to make a reference to a form property. The code is the following:

Private Sub cmdCautare_Click()
Dim strSQL As String, strOrder As String, strWhere As String
strSQL = "SELECT tblDosare.DosarID, tblDosare.DenumireDosar, tblDosare.CodDosar, tblDosare.DataDosar, tblInstante.Localitate, tblStadiu.Data, tblStadiu.Stadiu FROM tblInstante INNER JOIN (tblDosare INNER JOIN tblStadiu ON tblDosare.DosarID = tblStadiu.Dosar) ON tblInstante.InstantaID = tblDosare.Instanta"
strWhere = "WHERE"
strOrder = "ORDER BY DosarID"
If Not IsNull(Me.txtDenumire) Then
strWhere = strWhere & "(DenumireDosar) Like '*" & Me.txtDenumire & "*'"
End If
Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder
DoCmd.OpenForm "frmRezultateCautare", acNormal
End Sub

The problem is on this line:
Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder
The error message is the following: MS Access can't find the form 'frmRezultateCautare' referred to in a macro expression or Visual Basic code.
The form contains a list and is used to display the search results w.r. to the criteria from a main search form. If anyone can help i will apreciate it.

Thanks

View 1 Replies View Related

Error On Form!! HELP!

Mar 5, 2005

Hello Access World Pros!

I have a problem with some errors on a form/query that has been running circles around me for 4 hours now on a saturday night!!

Im trying to implement an error control message when a user does not select an employeeID on the main form, whilst they are on the subform. But with no joy! Also, I run into errors when a user enter the same item twice on the same subform! Access exclaims that there are duplicate values.

Please look at the simple access db attached and use the "acq" form. Maybe someone out there can enlighten me coz in all honesty im stumped!!

Cheerz :D

View 2 Replies View Related

QBF - Form Error

Mar 11, 2005

Help

I have been asked to amend an error occuring on a database which uses a QBF form to filter items in a very large table. The problem which is occuring, from what I can see, is that the first record in the table is automatically visible in the combo box which is used to select the filters.

If then someone changes the field in a combo box to filter on a different area the first record (ID) in the table is automatically edited.

I assume it is something to with Bound and unbound but dont know enough about access to know how to do it.

How can I allow users to select different options from a combo box without it amending data in the table.

Ange

View 2 Replies View Related

Error When I Use My Form

May 10, 2005

HI when i use my form I get the following error when i use one part of it. The error is

You can't reference a property or method for a control unless that control has focus


Can't someone help me please

Thanks
Chris Lynch

View 3 Replies View Related

Error When Using A Sub-form

Mar 20, 2006

I have created a subform where you select a vendor from a combo box and this then allows you to view all software of the selected vendor in a tabular view. When I use this form and then try to go into design view or close the form, i get the following error message:

'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again'

I have tried removing the index and changing the index to permit duplicates but this has not worked.

Could anyone tell me abit more about this error and how to get rid of it?

Many Thanks

View 2 Replies View Related

#error On Form

Nov 29, 2004

I Cannot Seem To Understan Why My Formulas On My Form Keep Giving Me The #error Message....

View 3 Replies View Related

Error In Total On Form

Mar 16, 2008

Okay, I know this is probably simple, but I am getting an error in putting a total on a form. I have the following:

Subform name is EntryDetail Subform
I have a field in the footer called TotalEntry that calculates the total entry. The formula is =sum([EntryAmt])

Form name is Entry
I have a textbox with the control =[Entry Subform].Form!TotalEntry

The text box shows #Name?

I am attaching a copy of the database.

Any ideas?

View 3 Replies View Related

Error When Opening Form

Jan 26, 2005

I have a button that opens another form to enter data on. But when I click the button I am getting the error msg: "Can't find project or library." And the debugger goes to the following sub routine and highlights Date in 3rd line(tried to bold).

This only happens on 1 user's machine. The other 4 computers open it flawlessly. All 5 have the same setup/security. Don't know why it works on 4 and not the last one. Any ideas?

(Access97.)
Private Sub Form_Open(Cancel As Integer)
Dim x As Long
Me.txtEntryDate.Value = Date
Me.lblErrMsg.Caption = ""

CurrentRateCode = ""
CurrentUnitFactor = 0
CurrentCartonFactor = 0

Set PTdb = CurrentDb()
Dim rsVASRate As Recordset

Set rsVASRate = PTdb.OpenRecordset("SELECT * FROM tblVASRateCodes", dbOpenDynaset)
x = 0
Do Until rsVASRate.EOF
VASRate(x).RateCode = rsVASRate(0)
VASRate(x).UnitFactor = rsVASRate("UnitValue")
VASRate(x).CartonFactor = rsVASRate("CartonValue")
x = x + 1
rsVASRate.MoveNext
Loop

VASRateTableCount = x - 1

rsVASRate.Close

DoCmd.RunSQL ("DELETE * FROM tblVASData") 'clear VAS data summary table

If TableExists("tblVASEntry") Then
DoCmd.RunSQL ("DELETE * FROM tblVASEntry") 'clear VAS data entry table
Me.subform_VASEntry.Requery
Else
DoCmd.RunSQL ("CREATE TABLE tblVASEntry (VEID IDENTITY PRIMARY, 'Rate Code' INTEGER, Units INTEGER, Cartons INTEGER);")
End If

End Sub

View 1 Replies View Related

Form DTpicker Error

Mar 31, 2005

I have a form with most of the fields being date fields. If I put a text box instead of DTpicker it works fine but when I use DTpicker it gives me an error like this "Can't set value to NULL when checkbox property = FALSE". And also when I use DTPicker it inputs time also which I don't like. I want only short date to be inputed in the table. Any idea why it is doing so. Can you please help me with this. Also suggest me if this is the right way of inputing data to the table or should I use ADO's or DAO's concept to input the data in the database.

I have to generate some reports based on this form. Could anyone suggest me please.

Any suggetions on this form it would be great!!

Thanks
vs

View 4 Replies View Related

Opening Form Error

Apr 6, 2005

Hi

My repair form pulls data from the vendor table to display the vendor information.
Whenever i try to open the repair form, it gives me a popup first that says "enter the extention number"
regardless of entering data or pressing cancel my form gets displayed.

When i enter a number (a random number) it wil populate my extention field along with all other data. And when i dont enter any number and hit the cancel button, it populates all data except the extention field, that has nothing to do with the number that i enter but is the one stored in the vendor table.

I am confused what to do with this
Can anyone help me

thanks

View 4 Replies View Related







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