I am trying to set the default sort code to something other than the primary key. Example- Book Code is Primary Key but I want the table to be sorted by Title. I have went into properties and used the Order By property and typed 'Book.Title' however when I view the datasheet, Book Code is still the 1st column??? Is it still being sorted by Title??
Any help would be greatly appreciated
Can't find any way to declare variables in the sort of module that Access uses by default.Having changed the default, I now get 'Option Compare Database' as the first line (previously nothing was there). Adding anything at all in that section, or changing it to 'Option Explicit' has the consistent effect that all the procedure names in the module are not recognised and nothing works.
Variables declared in procedures work only in the procedure even if declared as Public. Can't get a Static variable to work in more than one procedure. However many variables I declare, there are never any in the Declarations list at the top of the Code Window.
Hi
Is it possible to change the default table setting in Access 2000 for the 'Allow Zero Length' property from No to Yes? Or does anyone know any code that can be run to change any existing fields with Allow Zero Length = No to = Yes?
Any assistance would be much appreciated.
Thanks.
G'day,
I preface this question as most newbies do. I have just started using Access(2000) and have built rebuilt/converted from DOS a database for a basketball club. I did start from scratch and just inported the table data.
I have already made a number of forms and subforms, e.g. searching for a player displays all the other players in that team and displays the team staff from the relevant tables. So I understands the concepts.
What I have tried to do, and succeeded in part is to populate some of the fields of the Player Account records from the Fees table. I managed to do a query from the Fees table for this using a drop down for the FeeCode, which fills in the Fee Description and Fee Amount. However if I change the Fee Amount it changes all of the fee amounts for that Fee Code type. (I think I solved this myself as it is a query and that is what it should do, correct me if I am wrong). Note that all of the retrieved data is static except for the Fee Payment, which can be different amounts, this is where it changes the source. Also as it is a query I think , the retrieved data just shows on the screen and doesn't get written to the Accounts table (not the description though).
So I have obviously gone about this the wrong way and rebuilt the Accounts trying to use lookups. Here is my problem (finally you say). I cannot get the default value to lookup the fee table based on the fee code. Note I have not started on the VB side yet so please be easy on me. There is a drop down box for Fees Charged but this lists nothing, so like the query before nothing gets written to the Accounts table.
I think (maybe I don't) understand the lookup thing as I used an old (DOS) database before and did the same thing and was able to change the default value just for that account. i.e. lookup the table>field where the fee code is = to this. I have tried the subform wizard a few times but just cant get it right.
Should the lookup be in the table field or the subform. Am I on the right track and if so how do I get the retrieved info from the Fees table into the Accounts table. My end result should be something like this.
Fee Code(lookupFees - write to accounts), Description (lookupFees - display only), Fee Charged(lookupFees - write to accounts), Fees Paid(lookupfees - modify write to Accounts), Date(Accounts), Receipt Number(Accounts).
Thanks for taking the time to read this, I have tried to give as much information and hope I haven't given too much.
Regards
DrF :)
hi
i found this code here and it works IF the number comes first and is preceded by a letter
123AA
but it does not work if the letters come first
AA123
here's the code
Public Function GetString(WholeString As String) As String
Dim i As Integer
Dim Temp As String
Temp = CStr(WholeString)
For i = 1 To Len(WholeString)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetString = Mid(Temp, i)
Exit Function
End If
Next i
GetString = Temp
End Function
Public Function GetNumber(WholeString As String) As Double
Dim Temp As String
Dim i As Integer
Temp = CStr(WholeString)
For i = 1 To Len(Temp)
If InStr(1, "0123456789.", Mid(Temp, i, 1)) = 0 Then
GetNumber = Mid(Temp, 1, i - 1)
Exit Function
End If
Next i
GetNumber = Temp
End Function
the probelm is with this line but i'm not sure what it is
GetNumber = Mid(Temp, 1, i - 1)
(i also get runtime error 13) but my data is in the same format as the example i downloaded.
any ideas anyone?
thanks in advance and thanks to the person who created the code
Hello,
in my Access database there is quite a bit of VBA code.
I have set my LAN and file path in some of this code but will need to change it when this database will get to other users using a different LAN and path.
Is there a way I can avoid them to open the code and manually change this information?
I was thinking of some sort of text box where they will write their path (ex. \S01000sharedoffice*.*) and use a cmdbutton to overwrite the existing path?
Thanks.
High all.
I would like to know how to set a fields default value on a table.
Eg - i have a Yes/No field that and i want the default value to be set to 'Yes'.
I cant use the front end application because the form its on is a generic form used by about 30 other tables.
At present it is not set to anything and so always defaults to 'No' on the form.
Thanks in advance.
Why the code is showing error.
Private Sub Ref_Click()
Me.Ref.DefaultValue = Date
End Sub
I want that when I click the Ref field in the form the date field in the form get populated with todays date
This is getting into some stratospheric territory for me.
I am trying to change the visibility property of a label and corresponding text box, based on the contents of a different combo box. cboHow is a combo box that uses a value list as its row source, since the content of the list won't change. There are three fields that I need when the content of cboHow = "FIS". Otherwise I don't need them and I don't want to see them. My code is below:
Private Sub txtFISDepartmentNumber_BeforeUpdate(Cancel As Integer)
If cboHow <> "FIS" Then
lblFISDepartmentNumber.Visible = False
txtFISDepartmentNumber.Visible = False
Else: lblFISDepartmentNumber.Visible = True
txtFISDepartmentNumber.Visible = True
End Sub
At the moment there is no error code, but the label and text field don't respond by becoming visible/invisible when the contents of cboHow changes. Do I put this code with the txtFISDepartmentNumber or in cboHow? and do I use BeforeUpdate or AfterUpdate? Should I have a separate If..Then..Else for the label and the text box?
I have been playing around with this for several hours now and seem to be going in circles.
I am using A2K
I know that I can use an Alter Table query to add table fields from code.
But what if I just want to change a field from being Required or Not Required, or perhaps change the field width of a text field?
How do I do that from VBA?
convert the following into VBA code for an Access 2007 field on a form. It is needed to create a conditional default value based on another field's category: (Note: TransType, MembershipYear and Dues are field names and are all on the form). Based on the TransType (which is really a category of membership) I want the Dues field to have the applicable default value automatically entered.
For MembershipYear > 2008
If TransType="Individual" or TransType="I" Dues=35.00
If TransType="Family" or TransType="F" Dues=50.00
If TransType="Founder" Dues=100.00
If TransType="Student" Dues=10.00
If TransType="Lifetime" Dues="" or is Null
If (TransType is Null or TransType="") and TotalPaid >0, Dues=35.00
code doesn't wants to set the control default value
View 5 Replies View RelatedWhen I send a database to server it changes all of my special charters to unicod charterset, I suposed that server is on american charter set, I would like it to change to central europien windows - 1250 code page, is it posible and how? it is very importand to me.
thx
Is there a way to change the vertical scroll bar position to always be at the top when I move from form to form. Right now, it will start at the top, but when I move to a different form and back, the vertical scroll bar is positioned in the center. I turned off the autocenter property of the form and saved it in the desired position, but that doesn't work. My form is maximized when it opens. I would greatly appreciate some help. This has been an annoyance for quite some time now.
View 1 Replies View RelatedI'm changing a record source on a form when the user clicks a "save" button.
I'm doing this to store a value from the current form in a table that is not part of the default form query.
This works fine.
I have a separate button on my form called "Home", when the user clicks this button it requerys the current form which triggers the before update event to run, this in turn brings up a message box which asks the user if they want to save or not. If they click yes then it runs the SaveButton click() code.
For some reason when the user presses the save button and then exits, everything works but if a user presses the “home” button which triggers the save button then it brings up the “2107 The Value you entered doesn’t meet the validation rule defined for the field or control.” Runtime error and stops on the change recordsource command.
Why triggering the same code directly from a button or indirectly from a before update event has two different outcomes.
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
Hi all,
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
Thanks in advance for any responses.
D
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies View RelatedHi Everyone,
I hope someone can help.
I have a form with a combo boxes and a table with relevant list and additional field, fldDefaultDrive (Yes/No Field).
Currently in order to set the default value, I have used the following code for each default;
Private Sub Form_Load()
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """D"""
End Sub
However, I want users to be able to go into the table and change the default value if thier CD player default Drive is anything but D: Drive. I have tried to replace the D above with an SQL statement but with no success.
Private Sub Form_Load()
Dim Drivename As String
Drivename = SELECT tblMediaDrive.fldDrivename FROM tblMediaDrive WHERE (((tblMediaDrive.fldDefaultDrive)=-1));
Forms!frmMediaLabeller!CboDriveName.DefaultValue = """Drivename"""
End Sub
This is definetly not working, can anybody help, I have a feeling it is syntax but not sure where? :confused:
Robert88
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
Thanks
SHADOW
I was just wondering if it was possible to run a query by a specific value first, then the remainder of the following values in ascending order.
For example, the data would appear in the table as
Smith
Bloggs
Jones
Taylor
What I would like to do is have all the Taylors at the top, followed by Bloggs,Jones then Taylor
I have looked at the OrderBy function, but believe that you can only sort Ascending and Descending
Checked search, cant find anything
Many Thanks
Andy
Hi,
I try to do something but I don't know how.
I have 1 table with 3 rows (title, year, genre) and 1 Form.
Is it possible to create combobox or something with 3 criterium, when I selected
criterium to re-sort columns in Forms (by title, by year, by genre)?
thank you
Continuous form:
The standard menu / toolbar commands sort ASC and DEC work just fine.
However, if I define a button with the VBCode
"DoCmd.RunCommand acCmdSortAscending", I get an alarm message pop-up
saying "the command or action "SortAscending" isn't available now".
I made sure that the standard menu items "Sort asc / dec" are not grayed out(disabled) at the time I click the button in runtime. The mouse cursor points to the desired sort criteria field.
Any advice?
Regards from Belgium (pretty cold here)
I am completely stumped on this one. I have a database where we track IP Addresses and I just want to sort by IP...should be simple, I'd think.
With these 5 IP's, I'd want them in this order:
1.2.3.1
1.2.3.4
1.2.3.7
1.2.3.9
1.2.3.10
1.2.3.17
1.2.3.21
But it sorts in this order:
1.2.3.1
1.2.3.10
1.2.3.17
1.2.3.21
1.2.3.4
1.2.3.7
1.2.3.9
Is there anything I can do to fix this? I want it to see each number as a whole, for example "10" instead of "1" and "0"
Much thanks.
How do you change a file's sort order from ascii to international?
View 3 Replies View Related