Any way I can set a table up with an intrinsic limit, specifically that only ONE record at a time (this will vary) can possibly have value X selected from a lookup field containing assigned values X, Y and Z. Is this possible? If so, how do I do this?
View RepliesI have a list of customers. [ID] on (Form1)
I click_on their name [ID] that opens a dialogue (Form2 )specific to them.
I have a table of several dates with the relevant [ID]
I want a dropdown box on Form2 that only shows the dates for that specific customer[ID]
Ive tried [Form2].[ID] in the criteria of the ID field in the query for the dropdown data source.
I basically have General Date field (e.g. 10/1/2014 6:34:11 PM) and I want to limit the results to only a specific month and only to show reuslts after 6PM. I tried everything and still stuck.
View 2 Replies View RelatedHello, I have just spent ages doing searches and reading everything I can on locking. But, I have yet to find an answer as to how I can lock a specific field in a specific record.
e.g. Staff enter customer details, then at the end of the day the admin (me) checks it over and presses a big old button that stops them from locking certain fields in the current record only - they must still have access to the unlocked fields of the current record, and it must not lock any other records.
I'm guessing there's some VB code in the form of fieldname.lock = true, but then it locks the field throughout the whole table!
Can anyone tell me how to do this please?
I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.
Table
- BusArrivalTime
- BusDepartTime
Query
In my Query I'm making a new field like this. It correctly figures out the difference.
BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])
Report
Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.
=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")
Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source
=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")
...and it's summing my records, but the value is off by a few minutes and I cant figure out why.
In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform. The rowsource changes dependent on the value in another field (field2) in the same record. How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- I think I have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query.
Something like:
Lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);
me![field2] however does not function
I am having a problem restricting a user from a certain form.
I have a module that detects if a user or admin based on the roles entered in the employees table.
If their windows login is not listed in the employees table, they will not be able to view any of the forms.
Here's my problem, I want the users to access the switchboard, but they should not be able to get into the reporting button.
here's my code:
If LOAStatusbtn.Caption = "View Report" _
And Not admin _
Then
'useraccess denied because not an admin
error_text = "You do not have privileges to access this screen." & vbCr & "Please contact an administrator"
MsgBox error_text, , "No Privileges"
End If
If they click on LOAStatusbtn and their role is not admin, then a message should pop up.
I searched the forum and could not find anything similar to this.
Thank you,
how to do a particular thing in Access 2010 (I don't even know if it is possible).
I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")
So I can store, for each different product, none, one, or more options to let the customers choose from.
I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)
The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:
select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]
I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?
Is it possible to have a running total either in a query or using the Running Sum function on a text box on a report that will reset after a specific value. Here is what I would like to have happen:
The RunningTotalCube field to reset when it has reach 2.3 or whatever number comes closest to that number.
Date Time Item Cube RunningTotalCube
4-2-15 12:05 15615 0.5 0.5
4-2-15 12:06 15918 0.8 1.3
4-2-15 12:10 98563 0.5 1.8
4-2-15 12:12 45268 0.4 2.2
4-2-15 12:15 25854 0.9 0.9 {reset}
4-2-15 12:17 75136 0.5 1.4
Is this possible either in the query or the report/in Access or in VBA?
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I am trying to limit what a user can see when accessing a database in access. I am only testing with this in access before taking it to SQL server. I have 3 databases and I combined the data into one set of tables and now what i am trying to do is a certain user can only view data from a particular database.
I have a table with the users id and password, as well as an ID number from each database that I am trying to tie to the users.
for example one user can view and add/edit data in databases 1 and 3 but not 2. while there might be a user that can view and add/edit on 1 and 2 but not 3.
I created a template called "AD - Assembly". In this table I have fields for:
"AD_ RecNum" - This is the auto generated number field for each record, and also my Primary Key
"Document Number" - This is the number that is being assigned to each document placed in this table. (i.e. AD-0001-001)
"Description" - This is the description of each of the documents
"Originator" - This is who is assigning the document a number
"Origination Date" - This is a =CDate(Now()) field that will automatically generate the date and time the document was assigned a number.
I then created a form called "AD - Assembly". On this form I created a field called "Last Record". I want to open the "AD - Assembly" Form, and have this "Last Record" field automatically filled with the "Document Number" filed of the last created record based on the "Origination Date" field.
I want to be able to point my where clause to a specific record in a field when I double click on the record.
At present when I doubleClick it is defaulting to the Field but not the specific record.
I have an access form that needs filling in daily by various people.
It's to document changes to a website and I currently have a combobox box set up for the various sections to state whether they are AMENDS, REVERTS or NO CHANGE.
I have set conditional formatting to then highlight these sections but am also trying to get it to work so that if the user chooses "NO CHANGE" then the data for that field copies over from the previous record.
I have set this up in the AfterUpdate code for the combobox, but nothing is happening, not even an error....
Code:
Private Sub COMBOBOX1_AfterUpdate()
If Me.COMBOBOX1 = 3 Then
Me.[FIELD_TO_CHANGE] = DLookup("[FIELD_TO_CHANGE]", "tb_TABLE", "[ID]=Forms![form_FORM]![ID]-1")
End If
End Sub
(Where 3 is the value of NO CHANGE in the combobox, and FIELD_TO_CHANGE, tb_TABLE and form_FORM being the names of the various elements)...
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
An if statement which will disable out a field depending on which type of field is selected from a separate lookup.I know that is confusing, so let me explain with an example. If I have a lookup field connected to "Vegetables", "Fruits" and "Nuts". If the user selects a "Vegetable" from the lookup field, I need another field disabled, for example sake, "What fruit did you buy?". If the user selected a "Fruit" from the lookup, "What fruit did you buy?" would be enabled.
I know how to enable and disable fields, but it is the If Statement. How would you specify an If statement to specifically look at which category of a lookup is selected?The lookup is not static. So going with our example, the user can add more fruits, vegetables, and nuts as desired, so simply specifying the IDs for the available options will not work. The If statement needs to encompass the category.
How do I put an upper limit on the amount of time allowed in a date/time field?
For example - during a heart procedure a person is exposed to radiation, it will never be more than a total time of 10 minutes. I want to know how much, but I want data integrity so I don't want them to be able to enter more than 10 minutes.
is it possible to have a form page stay open for a time limit and then automatically close and open another form?
Reason I ask when a user clicks to open the database I want a splashscreen stating terms of use etc to stay open for a few seconds before it opens into the main database.
I want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedHi,
I'm trying to insert a record into an Access database from some ASP Code.
I have:
...
strSQL = "INSERT INTO tblUser (UserID, Date, Comments) VALUES " & _
(" & nID & ", #" & Now() & "#, " & "''" & strComments & "');"
conn.Execute(strSQL)
...
I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".
My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.
Any ideas as to what I might be doing wrong?
I have a drawing register database which notes the revisions for all drawings issued. One drawing can be revised several times so I have a details table which notes the revision letter, date issued and the notes for each revision. Using this table I created a max date query to show the latest issue date and revision for each drawing. This query is used for a transmittal form for issuing drawings. Both the form and report is working perfectly.
The TBLTransmittal consists of the following fields
ContractName
IssueDate
SubSup
DwgNo (multi-value checkbox)
Using the TBLDwgRegisterDtls or the query QRYDtldDwgReg (which includes the drawing titles) I would like to create a query that would look up the latest revision at the date of issue and show the revision letter.
I started a query combining TBLTransmittal and TBLDwgRegisterDtls and in the criteria for DateIssued of the TBLSDwgRegisterDtls (which is when the drawing was issued by the architect to the contractor) I put "Not > [IssueDate]". This is filtering the information to show revisions issued up to the date we (the contractor) issued the drawing to our sub-contractor but I need now to pull the max revision only from this information.
Before I go I will give you an example of what I want from the end result
Drawing BK2-02 rev. D was issued by the architect to the contractor on July 17th. This drawing was in-turn issued to our sub-contractor (Sub "A") this Saturday, August 9th. The same drawing was revised this morning and rev. E was issued to Sub "A" today, August 11th.
I want the end report to show that Sub A received Rev. D on 09-08-14 and Rev. E on 11-08-14.
I'm wondering if another max date query on this new query would work? I'll try and see what happens.
I think my question is fairly simple - fingers crossed.
I want to create a field that, whenever a record is added, it auto-adds the date & time the record was created. I'd call it Book_Date_Added, or something like that. I tried snooping around the Default Value options but couldn't figure it out.
The table exists and it already has some records, so I'd need to know how to add this field rather than how to create it when I create a table (although that would be helpful too, if it's somehow different).
I'd appreciate any help, thanks a bunch.
Greetings!
Hoping somone could help me here.
I am trying to create a database (duh!). At the moment, I have two tables. One table is a small timetable with the fields:
Day, Airline, Flight Number, Time, Origin, Destination, Aircraft
The other table (Delay) has the following fields:
Date, Day, Airline, Flight Number, Time, Origin, Destination, Delayed by.
I would like to do the following. In Delay I put in the date: 01/07/2005, fine. The Day (field format Date/Time dddd) is also 01/07/2005 which Access transofrms into the word "Friday". Airilne is BA. In Flight Numbers, I want the database to do a query on "TIMETABLE" to search all flights that operate on a Friday as some do not operate daily. (By the way in the 'Day' column of Timetable the values are "Monday Tuesday Wednesday Thursday Friday Saturday Sunday" or "Monday Thursday Saturday" depending on the flight). This query/lookup should be performed right after I type 01/07/2005 in the "day" field of "Table: Delay". A lookup combo box should be displayed when I arrive at the field FLight Number. I select a flight (e.g.: BA001) and automatically the Time, Origin and Destination of the flight should appear in the "Table: Delay".
I have been unsuccessful at even getting past the first hurdle. I tried a query of the "Table: Timetable" and fields "Day" and "Flight Number". The Criteria I do not know what to put in. "Like [Delay]![Day]" or "[Delay]![Day]" does not help and brings up nothing. However, "Like *Monday*" does bring up all the flights which operate on a Monday. However isn't there some way of telling the query to automatically occur after I type in the relevant day in the "Delay" table? Futhermore I would like to avoid (but not totally exclude if it's impossible) macros as eventually I hope to publish this to SQL or whatever.
Cheers in advance.
Hey all,
I have two fields 1 & 2
field 1 is a simple combo list of user defined values ie A, B, C or D
Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e
A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z
I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc
How do I do this?
Cheers all,
Matt :confused:
I am creating a training database and first I have tables in relationship to the courses:
tbl_Courses
Course ID (Primary Key)
Course Title
Objectives
Instructors (lookup field allowing multiple instructors to be selected, meaning they are skilled to teach the course)
tbl_Instructors
Instructor ID (Primary Key
Last Name
First Name
Full Name (Caluclated to put Last Name, First Name)
[code]....
What I am trying to do on a form to create a new event is once the user selects the Course from the Course ID combo box, then I need the Instructor combo box to only display the instructors who are skilled to teach the course which are selected in the tbl_Courses.
I can get all Instructor ID's, but not the names and the class that has multiple instructors show on one line.Should I have not put the Instructors field in the tbl_Courses? Do I need another table for instructor skills or something?
I'm currently trying to create a KPI report (Key Performance Indicator) to log jobs & their response times
I can easily produce a query that gathers all the information needed but I need to find a way to limit the results to 1 record per job as one job can have many records - Primary key being the TaskID field
I need a way to only pick out the oldest record & ignore the rest
For example
A priority 1 job that was logged at 01/05/2008 08:00 that needs to be responded to within 2 hours
Two engineers have carried out the work & logged the following details
1st Engineer responded at 01/05/2008 09:00 and the other returned later to complete the job at 01/05/2008 13:45
I need the query to only pick out the entry for 01/05/2008 09:00 as it's the oldest.
Any suggestions would be most welcome
this is what my query looks like at the moment.
SELECT tblTasks.TaskID, tblTasks.DateReported, DateAdd("h",DLookUp("[ResponceTime]","[tblPriorities]","[tblPriorities]![PrID]=" & [PrID]),[DateReported]) AS DueBY, tblTaskTime.TTStarted AS DateStarted, tblPriorities.Priority, tblPriorities.ResponceTime, [DateStarted]-[DateReported] AS Responded
FROM tblBuildings INNER JOIN (tblContracts INNER JOIN (tblPriorities INNER JOIN (tblTasks INNER JOIN tblTaskTime ON tblTasks.TaskID = tblTaskTime.TTLINKTask) ON tblPriorities.PrID = tblTasks.TaskPriority) ON tblContracts.ContID = tblTasks.ContractNo) ON tblBuildings.ProID = tblTasks.BuildingID
WHERE (((tblPriorities.Priority) Not Like "P8*" And (tblPriorities.Priority) Not Like "P5*" And (tblPriorities.Priority) Not Like "P3*" And (tblPriorities.Priority) Not Like "P6*"))
ORDER BY tblTaskTime.TTStarted;
Thanks