I am working on a scheduling form that uses 3 combo boxes to select the people being scheduled. The form is rowsourced to a table of training sessions with a field for each of three crew positions being trained. One combo box is used for each of those fields. The row source for each combo box is a query that returns a list of people due for training, not on vacation that date, correct crew position, etc.
The problem is this: I want to eliminate the people already sceduled in prior sessions from the list. I've tried to put a "not" criteria on the rowsource query but that did not work.
I've been away from Access for a few years but I vaugely remember problems of putting criteria on the field you are filling. Anyone have any ideas?
Now, in the amendment field, I've inserted a combo box, with 3 options in it. Let's say option A,B and C...The thing I want access to do, is, when option C is selected, I want fields Period, Salary and Shift to be automatically "Yes".But if option A or B is selected, the the user must choose what the other fields are going to be either Yes or No
I have a form that I want to filter out certain records based on if a field has data in it or not. I tried using a macro and putting the field equals "IsNotNull", but that didn't work. I just asked me what "IsNotNull" is suppose to be. correct way to do this via macro?
I tried searching but was finding VBA with other filters being applied (which is not the case).
I have a ticket database where tickets are added to access with no priority scores on them. Over time, meetings and discussions, those tickets are given scores through a form on my database. What I would like to do is somehow add a timestamp that shows exactly when a ticket went from being unscored, to having a priority score assigned to it. Note, before the ticket is scored; the Priority Score field is still blank. Null?
An idea I had was to somehow put a timestamp on the "Priority Score" field in my form that will only timestamp when that field goes from being blank to when it gets a number for the first time. It will not change the timestamp when that number is changed down the road. (We edit the priority scores, but I only want to know when the ticket was actually scored for the first time. This is for reporting purposes, how many new tickets we score each month, etc)
Could I do something along the lines of assigning code to the BeforeUpdate value of my Priority field on the form? Although I'm assuming this would put up a timestamp any time the field is altered afterwords also.
I haven't programmed using Access in about 10 years and seem to have lost all knowledge of it.I'm struggling to make a really simple application. I need to keep track of which serial number is attached to which order.All I want is for me to be able to scan a barcode (or manually type the numbers) into a 'packschein' (packing list) and then to scan all the barcodes of the products' serial numbers relating to this packing list. Then preferably simply press the enter button or even better scan a barcode which launches the code to save the new entry.
So a packing list can have 1 or more serial numbers.However, the way I've set it up, for some reason it requires a packschein number (good), but then does not require a serial number. I have this feeling I messed up with the way the tables are meant to be linking to one another.
I try to create an error message if a user fills in a date field and leaves a combo box with wrong value.I have no clue how to use "Is Not" to check the combo box.The date field that will be filled in is called "Sent to Check" and the combo box is called "Status Case" and the value should be "Sent For Check" If the value in the combo box is different, then the back ground should change to red and get a message.This is the code I have so far that does not work:
Code: Private Sub Test__date_started__AfterUpdate() 'XXXXXXXXXX working onXXXXXXXXXXXXXX If Not IsNull(Me.[Sent_To_Check] And Me.[Status_Case] IsNot "Sent For Check" Then MsgBox "Status Case be set to Sent For Check!", , "Incomplete Form!" Me.[Sent_To_Check].BackColor = RGB(255, 0, 0) Else Me.[Sent_To_Check].BackColor = RGB(255, 255, 255) End If End Sub
I would like to define a field (mailingaddresscounty) as type lookup using a field (county) from another table (Zip). But instead of the user selecting from a long list, I would like the selection to be automatically made based on the value of another field (mailingaddresszipcode).The two tables are Organizations and Zips. The user enters the 9 digit zipcode in the organization table (mailingaddresszipcode). I would like the county field in the organization table (mailingaddresscounty) to draw from (link to?) the corresponding 5 digit zipcode in the Zip table (zip) and return the correct county for that zipcode.
I have a query, that I have a criteria to show appointments in the past (< Date()) but one result doesn't show up although the appointment end date is a past date, it only shows up when I fill in a field that is in another table that is joined and part of the query. But there's no criteria there for it to not be null.
1. I have a switchboard. I want to click a button, that opens a form with a dropdown list, when I make my selection, it opens a subform in add mode, but the linked field in the subform isn't empty, but filled with the mainform's field value that I selected?
OR
2. Is there a way for me to open a form in add mode, add data to it, click the add button (I will add an add button) that allows me to add again, but this time a particular field is not empty, but filled with selected info from previous selection?
Say for instance, I have 2 fields (both combo box fields), I click add, made selections for both fields, I click add again, but this time one of the fields stays constant like it's already been selected. It's filled with what was selected from before.
I am trying to open a word document with corresponding data filled from the text field of a form. I managed to get the word document but I don't know how to give a variable in word document.
I'm very confused with this "first" and "last" criteria
I can easily understand for "max" "min" "sum" etc ... but not the "first" and "last"
I import monthly data for the year 2013 and for each month I import, it will have a record showing 201301 (for january 2013), 201302 (for february 2013) etc... until today which is 201312
I import them in order that is: 201301 first till the last one which is 201312
Now is the Criteria "first" give me the date at which it is First entered in the table or just the first that he see in the table? ( can putting them in order either a->z or z<-a affect this criteria?) or what if I put import them not in order but randomly like?
When I used last it didn't give me 201312 but 201311...
I have one datatable and i have to list the duplicates from it based on different criterias. It contains invoices, we would like to avoid duplicated payments.I'm struggling with a query. Basically i have a working one, i have to add one more criteria to filter out a couple of rows where the text column is "freight", but anyhow i have tried, it's not working. Without that one line where the not equal is, it's working as it should, when i'm adding that line it ruturns the same as before, but it should give me 108 less lines. The below one is based on an other query result called Duplicates_Rule1.Here is the query:
Code: SELECT * FROM Duplicates_Rule1 WHERE (((Duplicates_Rule1.BSEG_DocumentNo) In (SELECT [BSEG_DocumentNo] FROM [Duplicates_Rule1] As Tmp GROUP BY [BSEG_DocumentNo] HAVING Count(*)=1 )) AND (Duplicates_Rule1.Check) Is Null) and (Duplicates_Rule1.BSAK_Text <> "FREIGHT") ORDER BY Duplicates_Rule1.BSEG_DocumentNo;
I have a query I'm working with that finds data that changes, calculates the numbers, then prints a result in another column. The problem is that if I don't have any changes, then I have a blank result. Normally this wouldn't be a problem, but I'm making reports available to other users and would like this populated.
What seems simplest is to add an expression in the criteria field for the column that displays data after crunching numbers. Since this only has a display if something has changed, then I need an expression added if there is no data. Can I add something like an IsNull expression to display the results from another column in the same table? The data will end up being redundant, but I'm ok with that for now. the report should probably have the expression, but the column is already here in this table.
I've got a query that does exactly what this (http://www.access-programmers.co.uk/forums/showthread.php?t=89564&highlight=null+records) chap got his to do.
However I want to add a "*" character to the criteria to allow for searches with partial matches. Here's the criteria that works:
[title]=[Forms].[Search]![Title] Or [Forms].[Search]![Title] Is Null
However when I try the logical extension:
[title]=[Forms].[Search]![Title] & "*" Or [Forms].[Search]![Title] Is Null
this doesn't work, and nor does:
[title]=([Forms].[Search]![Title] & "*") Or [Forms].[Search]![Title] Is Null
WHAT DO I DO? I'M TIRED AND I CANT THINK STRAIGHT! :eek: thanks and sorry for being so dumb!
I need to be able to add x months to a given date which I'm using in a criteria expression. I've figured out that I can just add y number of days, but the answers aren't quite accurate across different ranges of months.
I'm after something like <[BeginDate] And >([BeginDate]+[3months])
but I haven't turned up anything useful in an hour of googling - finding it difficult to define what I want in search engine terms.
I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".
I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.
I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...
SELECT [Dove Data File].* FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL FROM Dove WHERE [Dove Data File].TowerBase = Dove.TowerBase AND [Dove Data File].Bells <> Dove.Bells))=True));
But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.
I am trying to map certifications done by colleagues in my department.There are 4 certifications and I have which I have pulled out from the Certifications tables using individual query for each certification.Now, I want to add "Certification-Name_Certified" col which will have "yes" or "no" values for each certification to the master data of the department personnel as it only have unique records using a query.
I have pop up form for report selection. In my drop down i have certain reports that need to have start and end date.
I'm using this formula to show or hide start date field and end date field.
If Not [Report_Selection] = "VehiclesNotRecovered" Then StartDate.Visible = True EndDate.Visible = True
Else StartDate.Visible = False EndDate.Visible = False End If
I would like to give the user a pop message to warn him/her fill in the start and end date field when selecting reports other than VehiclesNotRecovered.
If startdate and enddate fields not filled in and they run a report - they will get an #error message.
I have a form with datasheet view and I need to make user that user fills in all fields before he moves on to the next row. how to catch the moment of moving to another row?
I created a subform in side a form where I have some data. The idea is to fill in the row with a color if a value is met, like "Reset".Conditional format is working (for one field), but in Access 2003 I can only have 3, and I have like 6 values to choose from.I use the below code to no avail:
Code:
Option Compare Database Private Sub Form_AfterUpdate() If Me.Legend = "Reset" Then Me.Legend.BackColor = vbRed End If End Sub
One of the managers has requested to set up a form to enter the date at the top of the form and then enter in data for all the plants below for that day. I thought I could just use a form/subform combo with the date (and any other information repeated to all the plants), but the catch is, he wants a list of the plants to automatically populate when he enters a date.
The table would look something like this:
Form
Date
---------------------------------------
Subform (with all Plant names pre-filled for this date -- same list each time--and allowing him to fill in Sales and On Time Delivery).
Plant___________Sales_________On Time Delivery Indianapolis Detroit Memphis Tampa
