Forms :: Non Matching Records To Give Message Box?
Dec 11, 2013
I have two tables, categories and items. I have a form that is linked to the categories table and a subform that is linked to the items table. The user selects a category on the main form then an item to add to the category on the subform. (all of this is functioning correctly - but you need to understand for my question.)
Both the form and the subform have a text box that displays a record from their respective table, for the example we will call this color.
What I need to do is create code that checks the color text box from the category (main form) against the text box for the item (subform) and if they are not a match I need to display a message box - from where I can then add the necessary actions.
EDIT: note this is not a query on an entire table or 2 it is just comparing the two text boxes on the form and subform
I have a table that is a list of available UPC codes that through forms are being assigned to several different tables (types of items). When the UPC code is assigned, there is a yes/no box which is checked. I need to have a message box appear when the last of these UPC codes has been used (or better yet perhaps when there are a specific number left - such as 10 - so the user isn't all of a sudden locked out of any more work).
It could even be triggered when a specific UPC code is reached - they will be in numerical order (ignoring the check digit). Is there anyway to do this globally or would the check need to be on each form as the UPC is assigned?
I have a form that is bound to a query that returns all records matching a set criteria (namely, the field ApprovalDate is null); the idea is that the user clicks on a command button which gives information, and if the user chooses to continue (vbYes), then the field ApprovalDate has its value set as the current date, and the record is saved.
Next, I have put in DoCmd.Requery to requery the form and effectively go to the next record wanting approval - this appears to work fine, however I need to add something to the code that handles a null return from the requery - preferably a message box and then have the form close.
Is it possible to use a command button (update) to update matching records in a form (Test)? I have a file (april.xlsx) in which the first column Cytogenetics ID (14-xxxx) is a unique identifier that matches a record in the database. The next column Result (NL-F, NL-M, F-VUS, M-VUS, A-M, A-F) and the date column next to it are what need to be updated in that record. In the database there is a field called Result that needs to be updated with the text from column 2:
NL-F and NL-M = Normal VUS-F and VUS-M = Variant of Unknown Sig. A-M and A-F = Abnormal
There is also a Final TAT Date field in the database that needs to be updated with column 3 in the spreadsheet (Final TAT Date).So basically when there is a match with column 1 in april.txt to a record in the database, the Result field in the database is updated with column 2 of april.txt and the Final TAT Date field in the database is updated with column 3 of april.txt.
Cytogenetics ID Result Final TAT Date 14-0390 Normal 4/11/2014 14-0396 Variant of Unknown Sig 4/18/2014
All. using 2010. I have a form and a subform. my master and child links are set but when I scroll thru my form; my subform does not change to show the matching records. I do have an unbound search field for the same field as the master and child links. When I type in the search field for a record; the subform does change to that particular record. Do you think that has anything to do with it?
A very elementary question - but I'd be grateful for an answer.
I have two tables (or perhaps two queries) each with a key field. If all is well, there should be complete correspondence between the two sets of records. That is, if there's a record with key 12345 in one table, there should also be a record with key 12345 in the other table.
I'm looking for the simplest way of checking whether or not this is the case, and, if it's not, detecting which records in one table are unmatched by any record in the other.
I have a database called LettersDatabase this databse holds all the letters that have been made including the path to the doc. I use SSN to ID the letters to customers on the Contacts Database.
Contacts database also uses the SSN to id the contacts
I have a form that creates new letters for customers in this form I have listbox that queries the LettersDatabase for all matching records based on the forms contact SSN to see how many letters have been made for that customer.
The problem is that my listbox only shows the first record matching that SSN but there are more records in that LettersDatabase with the same SSN that I need to have diplayed on the listbox as well.
I may be writing the query incorrectly. Here what I have for the query on the listbox
Like[Forms]![LetterMaker]![txtSSN]
I try adding (&"*") to the end of the query but that does not help.
If anyone out there has the solution to this problem it would be greatly appreciated
I have a table which holds information relating to a fleet of vehicles; each of these vehicles is currently physically checked on a daily basis for a number of items, one of which being to check that the vehicle has valid VED (tax disc).
With the paper disc being abolished now, we have a situation where the individual using the vehicle cannot be certain about its VED status (the chances of one not being taxed is next to nil, but we are talking a fleet of nearly 1000 vehicles here, so anything is possible)
Our current database has most of the vehicle information stored already, so in theory I think I only need to add a couple of fields, which will make the table structure (roughly):
Is it possible that when a Form (not related to the table, and most likely the main switchboard form) is opened that both the MOTExpiry and VEDExpiry dates are checked, and if either or both of them are due to expire within 7 days or have already expired that a warning appears (vbaOKOnly messagebox would do fine) stating that there are vehicles that require their MOT & VED dates to be checked and updated accordingly)?
I need to have the sum of the "matching records" of a subform, exported to a variable of the main form, in order to use it in an if condition.
e.g. "IF a client has brought X? times the vehicle A for a service of type B, THEN do ..." How do I get the X value in a variable within the main form which presents all activity for all clients (by means of a subform).
I hope this is clear. Plaese help me, guys. You 've done it before, you're so great!
I have two tables Table 1 and Table 2. Table 1 has 6 fields 175 records and Table 2 has 4 fields and 330 records. The first three fields in both the tables are the same. I need to find all records in the first table that have a matching record in the second table based on those first three fields. How can I accomplish this? Any help would be greatly appreciated.
Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.
I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:
DELETE tblB.* from tblB INNER JOIN tblA ON tblB.Field1 = tblA.Field1 AND tblB.Field2 = tblA.Field2 AND tblB.Field3 = tblA.Field3...
but I get "Could not delete from specified tables".
What I would like to do is create a query which returns a list of all the holidays with a field showing how many bookings have been created for each holiday. This almost works:
SELECT Count(1) AS CountOfBookingID, Bookings.HolidayID FROM Bookings GROUP BY Bookings.HolidayID;
...but it does not display holidays where there are no bookings.
Is it possible to create one which will show all holidays even if there are no bookings?
I have database that i am working on , i want to give a user a time limited accsess ( one week ) to the reports section for example !!
after a certain date he will require a password or the report button will be inactive ...
-at program start up , it will check for today's date if it is greater or equal to (lets say ) 24-9-2013 then it will change a field in an X table from "yes" to "no" ,
-after that the code will check table X for the yes no field , if yes then open Form A if no then open form B / or make report button inactive
how to do that in VB ... and is there is a better way to do it ? can it be done in the macro builder ?
Hi, I've been trying to get this for ages now - both in the design view and in sql:
I have 2 tables - one called DrawingsRegister and a related one called DrawingRevisions. Each drawing has one or more drawing revisions. I want a query that will show each drawing (just once) that has more than one revision:
I have a form where a user reviews information input by another user, once they have done this they sign it off by selecting their name from a drop down list. On this form there is a scrolling message which tells the user how many un-signed entries there are. This works fine until there are 0 (zero) entries to be signed off.
The scrolling message is linked to a count query which basically counts any records that does not have a name entered in the required field. I have done this with ' Not Like "*" '
Like I say it works when there is one or more entries to count, but as soon as there are none the query does not output a zero it is just blank. This is the problem.
I need some sort of statement to say if there are no matching records please display a zero.
I am trying to create a query in Access 2010 for records that don't match based on the following criteria. I have two tables with identical ID's and I need to do a comparison on the amount field between both tables and only return the records from one table displaying all fields plus the amount fields that do not equal. I tried this in the wizard unsuccessfully.
I have a strange problem that has me beaten. I open an Access 2003 table, sort on field "Job Number", click the Find button and enter a job number. I expect to find 30 records, but Find only hits 8 of them. If I over-type the Job Number (with the same numerics), Find is then able to return this record. If I place my cursor in ANY of the 30 records and click Filter by Selection, all 30 records are included. I thought there may be a non-printable character or a space hidden at the end of the field, but I expect both Filter and Find would return the same records. Even more strangely, when my client zips the MDB file and emails it to me, this behaviour does not occur on my PC. Has anyone else comes across this behaviour? If so, can you please point me in the direction that will help me find a solution. cheers, Mark Chimes
Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically
Field1 ID is an auto record ID, field2 ID is actually field1 ID assigned that record, in other words record 1 has a roommate (record 5) assigned to it, record 2 has a roommate (record 4) assigned to it
Table A
Field1 ID Field2 ID 1 5 2 4 3 6 4 2 5 1 6 8
Results records I'm looking to display for would be: 1 5 2 4 3 6 6 8
I only want to display all fields for records, but I don't want to show their matching record, so I want to display record1, but not record 5 because record 1 has record 5 as a roommate, want to display record 2 but not record 4.
I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.
Code: Name EMP ID OP1 OP2 OP3 OP4 OP5 ----------------------------------------------------------------------------- John Doe 526261 C C C Bob Doe 555622 C C C Sheila Doe 066600 C C C
Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.
