On an existing DB i have set a default value for a field. The customer has now asked that they be able to change this default. I thought, hey, no big problem. They have SuperUsers, so i included a little pop up form that lists all the current valid values for that field (from a look up table). If the default value needs to change a superuser can access this and select the new default. That works fine. The trouble comes when setting the default value on a form.
I figured a simple bit of VBA in the OnOpen event - Me.cmbTypes.Value = Me.cmbTypes.DefaultValue - should work. However the value it returns from the default is ""xxxx"" and as such it is read as "". I can work around it by removing the extra " " but WHY does it do this? Would it be better to set this up as a custom property of the DB?
Could someone tell me if I need to break this select statement down? I'm running into problems with the DMax statement since it requires double quotes around it's arguments. When the parser hits the first argument, it closes the string like it's supposed to, but not the way I want it to.
In essence, the query returns 4 fields: unique ID, week number, computed score (TotSum), and the computed handicap (which is half the difference between your score and the high scorer):
Quote: strSQL = "SELECT tblRoster.HEDR, tblScores.WeekNo, " & _ "[A1T1]+[A1T2]+[A1T3]+[A2T1]+[A2T2]+[A2T3]+[A3T1]+[A3T2]+[A3T3] AS TotSum, " & _ "Round(((DMax("[TotSum]", "[qryHandicap]") - [TotSum]) / 2) + 0.1) AS Handicap " & _ "FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _ "WHERE (((tblScores.WeekNo) = " & inpWeekNum & ") And (TotSum > 0)) " & _ "ORDER BY TotSum DESC;"
What would the proper syntax be and/or is there a better way to obtain this information. After acquired, it is being salted away in a table which is recomputed after each competition.
Thanks for the input.
-Brian.
p.s. I know storing computed fields is a big no-no, but if you do not compete in a given week, the handicap from the last time you participated is used. This table will store the participants handicap as well as the last competition they were in. It seemed easier this way.
I have a button that generates text according to information in different fields. The text has multiple lines and when I select the button to generate the text it puts all the lines together when I need it to do a new paragraph after each line.
Sample: Me.GeneratedCode = "This is a test for " & Me.ServerName & "." & _ "The server " & Me.TestResults & " the test."
The above shows up in one continues line. This is a test for shankserver. The server Passed the test.
I what it to look like this
This is a test for shankserver. The server Passed the test.
My DB has one main table where all the records are stored, and one form with multiple tabs allowing for data entry and editing. There are two distinct "Data Types" I have in the tables, so on the two distinct form tabs, I created a text box with a default value for each one (if it's a Type A record, then the box has a default value of "Type A" and the same rules for Type B).
Here's the problem. It doesn't matter if I include the default value in the Form Properties Default Value field or if I do in VBA (using this method), when a new record is entered into the table, it makes them all default Type B records.
In the database attached I have a form called from Candidates.In this form is a combo box where a user can select what company the candidate belongs too.
However some candidates are Private Individuals (do not belong to a company).Therefore, I want the Position field to be set to 'n/a" by default if 'Private Individual' is selected.
On Microsoft Access 2010, I am creating a database project for a DJ booking system (not for professional use) and wondered how you can stop double booking from occurring that would provide you with an error message that I could personalize.
My current booking file looks like this:
Booking ID - Primary Key Customer ID (Linked to customer table) Venue ID (Linked to venue table) Date Booked Set Up Time Start Time Finish Time Party Type Discount Code
At the moment I have the date booked as Indexed (No Duplicates) however this gives me a long error message that I can not personalise.
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
I am new to Access and have made a database for a shuttle company that keeps track of their bookings. I need to prevent from the same booking being entered twice. I have a "Booking Form" that was made from a table "Master List". I was wondering if there was a way to compare three of the fields and if they match then a error would pop up.
I think if the "client's name", "booking date", and "pick up time" matched then a "booking already exists"... and also there are four different people can enter data and they have a log in form how can I get their "username" to be put into a field on the "Master List" automatically...
Is there any way how to create reliable link between Excel and Access which would work reversibly? I mean, if I change the data in Access (table) they will change in Excel spreadsheet and conversely?
I need to make a new table from an existing table. Each field in the output table must have a leading and trailing quote. I tried to surround the quotes with quotes (" " ") Acess says it's invalid.
I currently have a listbox. (list32) that is populated with results of a query. I would like to doubleclick on an item and have it open a form that displays the listbox record i clicked on and a "notes" field. Its my understanding that the table fields are limited to 255 chars so i was curious if I could make the notes field reside somewhere else. I will need the notes able to be edited, specific to record entry, and then saved on exit. Is this doable?
Is it possible to load the data in input from by double clicking on data from list box ?
I have single form on which both input form and read-only form is present. as i used visible property to display or not accordingly.
User enters the data from input form. (It has been done)
User go onto read only form where combo box and list box. from drop down values load into list-box. (It has been done)
Now double click on any record then it should re-directed on input form with loading the data in editable mode for updation purpose. ???? (How this step will be done) ?
I have a mainform named EMA and a subform named Q_EMO. In the mainform there is one event related to a patient.In the subform Q_EMO there are all the events releted to the patient.I would like to get a record in the mainform EMA when I double click in the record of the subform.
Apologies, this was posted as a reply to a previous thread I submitted but its not related to the title anymore (dlookups).
I have a query that looks up a payrate value for an employee in a payrate table. It uses the appropriate value to determine the pay rate at teh time an employee has worked (DateWorked). On top of this I also need to introduce a parameter criteria on the DateWorked to allow the user to run the report for a time frame. This is where I get stuck as I cant seem to get it to work properly if I introduce another criteria on the field
The query that works great (without the second criteria) is as follows;
SELECT tblTimeCard.EmployeeId, tblTimeCard.TimeCardId, tblTimeCardHours.Task, tblTimeCardHours.Hours, tblTimeCard.DateWorked, tblPayRates.DateFrom, tblPayRates.DateTo, tblPayRates.PayRate, tblTimeCardHours.Hours*tblPayRates.PayRate AS Cost FROM (tblTimeCard INNER JOIN tblTimeCardHours ON tblTimeCard.TimeCardId = tblTimeCardHours.TimeCardId) INNER JOIN tblPayRates ON tblTimeCard.EmployeeId = tblPayRates.EmployeeId WHERE tblTimeCard.DateWorked Between [tblPayRates].[DateFrom] And [tblPayRates].[DateTo];
This is where I need to introduce another criteria where tbl.TimeCard.DateWorked Between [Enter Date From] And[Enter Date To]
when I try this I must have one of the joins wrong as I get all the payrate values for each date worked even if they are outside the dateworked criteria set by the second criteria.. I hope I have explianed this ok. Can someone please advise where I should be looking - many thanks
Is there any way in which I can automate a query (or update table query) to find the next date stamp that always starts with 'DATE', and put a double return in before it (so it starts on a new line with a spare line between). This is a table with 2 fields in it, the first being a Customer Reference number, and the second being a memo field, and some of the details and dates are quite vast. As an example the following text that is currently being displayed in the memo field would be displayed as the section below.
From this:- DATE: 13/11/2007 TIME: 2:24 PM:went to pay with CC DATE: 09/10/2006 TIME: 3:41 PM:Sent Invoice to Scott DATE: 10/10/2005 TIME: 10:22 AM:Email follow up to obtain feedback DATE: 25/10/1999 TIME: 11:18 AM:Received all documentation
To this:- DATE: 13/11/2007 TIME: 2:24 PM:went to pay with CC
DATE: 09/10/2006 TIME: 3:41 PM:Sent Invoice to Scott
DATE: 10/10/2005 TIME: 10:22 AM:Email follow up to obtain feedback
DATE: 25/10/1999 TIME: 11:18 AM:Received all documentation
I need created a form frmFlight this form has two fields called DEP and ARR what I need is when the users types the 3 letter code in the DEP or ARR field if these do not match the any records in field AirportCode in the table tblAirport will highlite a error. Also I need to double click on the field DEP or ARR in the frmFlight and open the frmAirport with the specific record. Example if one of the records contain LCY by double clicking it will open the form frmAirport displaying the LCY record.
Is it possible to set a double input mask for a field? I would to set it so users can eneter two different values for data field: 00/00/0000 or just the year 0000, is it possible? How do I do that?
For some reason I cannot enter decimal values without the number rounding up or down. I've already set it to 'Number' and 'double' and have also tried 'Fixed' set to 2 decimal places. Right now the only way it works is if I set it to 'Text'. However I need to do some calculations with these values eventually.
What's strange is I have another table in the same database that is NOT rounding and is working just fine.
I have a form that utilizes a combo box with 2 options: 0 & 0.5. If I select 0.5 on the combo box the field in the table records it as 2. Why doesn't it reflect as 0.5? And how do I fix it?
This is the general info that I have for the field properties:
Field Size - Double Decimal Places - 1 Default Value - 0
