Forms :: Assigning Matching IDs From One Table To Another - Compliance Testing DB
Nov 11, 2013
A while ago I was tasked with creating a compliance testing database for my company's internal processes. For example: Payments, Hiring, Project Management, etc. Right now it's all done through Excel and is structured like this:
In short, a Test has many Items (based on company regulations, like "Has the project been approved by all superiors?"). For each Item you choose a few Samples ("Project A", "Project B" and so on), and for each Sample the user attributes a Grade ("In Compliance", "Not In Compliance", "N/A", etc).
In the Tests mainform, the user fills in the Test details (Tab #1), chooses which available Items he wants to include in his test (Tab #2), details the Samples which will be tested (Tab #3) and finally assigns a Grade to each Item/Sample combination (Tab #4).
Here's what I can't figure out:
In the attached DB, if we go into the "CT - Projects 2013" and check the Samples in Tab#3, we can see they match perfectly with the ones at Tab#4.
Now if we go into "CT - Projects 2014" and do the same thing, we can see that the Samples assigned to the Items in Tab#4 don't match up with the ones in Tab#3 at all. This is because the DB is checking only if the Samples' ItemIDs match with the ItemIDs in the test. But it also needs to make sure that the TestID, the Item's TestID *and* the Sample's TestID match, or it just won't work!
I attached a 2003 version of the database as the first upload used Access 2010.
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
I have created a login form and create user page form, the tblListofUsers has two columns, UserName and Password, on the log in form when a user enters username and password, it should be matched with the tbllistofusers if true then msgbox"Welcome" else Msgbox "wrong username or password", I have written following code but gives error
Private Sub Command5_Click() If DLookup("UserName", "tblListOfUsers", "UserName = " & Forms![loginpageForm]!User) And DLookup("Password", "tblListOfUsers", "Password = " & Forms![loginpageForm]!passworduser) Then MsgBox "You welcome" Else MsgBox "Wrong username or password" End If End Sub
I do not want to allow the main form record to be submitted unless there is at least one subform entry.Initially I thought it would be as simple as a "Before Update" event which would test the record count of the subform. This works... kind of... not really. It does check successfully, however the conundrum is that if the user has completed the main form and tries clicking INTO the subform to add records, this fires the Before Update event and Cancels the update event.
How can I force a user to complete the subform without running into this issue? I dunno how easy this is to do considering you kind of HAVE to exit the main form to enter the subform, at which point the record HAS to be saved...
I have a form with two list boxes on it: listAllBANANA and listActiveBANANA
The listboxes are populated from tblBANANA. Active is defined by the tblBANANA field "ResponseDate" being Null.
Also on the form is a CloseBANANA command button. When pressed with no selection, it opens frmCloseBANANA to the first record. If a BANANA is selected in listActiveBANANA, the form opens to the selected record.
Now comes the tricky part: if a BANANA is selected in listAllBANANA, I first need to verify they have an Active BANANA selected, before opening the form. I've poked and tested and searched and cut and pasted my little fingers off with no joy. What I've deduced is that I'm either messing up the syntax of the DLookup or I'm incorrectly referencing the selected record. There's also the third option that I'm going about this in entirely the wrong way.
All other code below works. If I delete or comment out the line in red, I have no issues other than I may open up a blank form because I'm trying to close a BANANA that is already closed.
Code: 'If a BANANA is selected in Active BANANA or All BANANA, open Close BANANA to that record. If not, open form to most recent record Private Sub cmdCloseBANANA_Click() If Not IsNull(Forms!frmGUI!listActiveBANANA) Then
I've created and tested my database. What step or steps do I take now to make it so it's a program that only shows the forms and menu's I created, not all the Tables, Queries etc and the formatting options? Is there a link that explains what to do step by step?
Database has a form to add a new item. This info is stored in the Item TBL. There is also a table that lists available UPC codes called UPC TBL. When a user enters a new item a dialog box will give them the choice to add a UPC code for that item. Clicking no - nothing changes and user return to the original form (no brainer) But if they click yes what I need to happen is:
1. The next available UPC code in the UPC TBL is entered into the Item TBL field for UPC.
2. The UPC TBL is updated to remove the assigned UPC, leaving it ready for the next assignment.
3. - and for another day - I need to create a message when the UPC table is empty because all UPC's have been assigned.
There's an inventory of products that I might take to a show (convention). What's a good way to associate the show with the product, and store that relationship in the ShowLink table?
I could make a datasheet with one of the fields a combo box, but this would get tedious if I had 100 products. I figure a better way would be to have a multiselect list box that I select all the products, then have one combo box to associate a show to it.
The following code is in the Form Current. As you can see if BranchCode = 2 I want to do a DLookup on the SystemPreferences table that holds a number Branch fields that hold document archive numbers for that branch. I want to move the number in the CroArchiveNo field into the field ArchiveNumber. I then run an Update Query on the system preferences table to add one to the relative branch archive number.
If Me.[BranchCode] = 2 Then Me.[ArchiveNumber] = DLookup("[CroArchiveNo]", "SystemPreferences", "[SysPrefId] = 1") DoCmd.SetWarnings False DoCmd.OpenQuery "UpdateCroArchiveNo" DoCmd.SetWarnings True End If
My problem is that I get an error
Run Time error 2448 You can't assign a value to this object
I have a form, its based on a query. That query contains an Autonumber(intID) from a Table(customers). I also have another table(dealers), that uses the ID number as well. Next to the dealer info, I can click the "+" and it'll show all customers pertaining to that dealer.
I've seen some that have had different ID numbers. In the form, one has the ID of 222...while in the Dealer Table it has the ID of 227.
Does anyone know what the problem is?
Also...I have a subform where you can add a dealer to this Dealer Table if it does not exist in the combo box. Here's the code:
If MsgBox("Add New Dealer Number?", vbYesNo, "Warning") = vbYes Then DoCmd.OpenForm "DealerContact", acNormal, , , acFormAdd, acDialog, NewData Response = acDataErrAdded End If
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".
I have two tables. One with a persons name and ID, DOB, GP etc (TblName) and one with the persons ID and medication they take (TblMedication). I have a query that runs a report. The header is made up of the first table (TblName) and the body of the second table (TblMedication). All works fine but it shows all records from the first table (TblName) regardless of whether they have medication or not so the body shows up blank. I want to run the query with both tables information but only show the persons header if they have medication.
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 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 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.
I have a bit of code that upon a button click will check to see how many records match (CustomerID); if there are no records returned it goes to another action (works fine), and if there is a record returned, it opens another form to that record (again, works fine).
However, should there be more than one record, how do I get it to open the first one?
Once the form is open, what code would I use for a command button to find/goto next matching record (as I haven't used .FindRecord in the first instance, .FindNext won't work - or will it?)
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 table. Also i have a text file which some of the fields are matching with my table fields ( lets say field A and B ). Now, i need to do compare of these A & B of my table against A & B of the text file and give a result as follows;
1. "Field A" not in the table but in the text file (un-matching data to be shown ) 2. "Field A" not in the text file but in the table (un-matching data to be shown ) 3. "field B" mismatches
I have three tables: First, Second, and Third.The tables Second and Third each have a column with a default value of "X".I'm creating a query that selects fields from First table that JOIN with corresponding fields from Second and Third, so that all records from First are represented and only matching records from Second and Thirdare returned. I also select the "X" fields from Second and Third.
So I run the query: and the "X" column from Second only displays "X" for records that have a match in Second (since I mean for these fields to be quick indicators for if the record also appears in the other tables, this is my ideal result). However, the "X" column from Thirddisplays "X" for all rows, regardless of whether the record has a match in Third or not.
I can get more specific if need be, but I really can't tell why the one is displaying according to matching records but the other is always on. The JOINs are set correctly and the properties for the columns all check out.
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
The table ProductInfo has an ItemID, and its corresponding Quantity.The table Orders has a PO column, an Item# column, a OrderQuantity column, and the column Item#CurrentStock which holds the Quantity of the selected item, for 3 items.I have created a form that has a ComboBox populated with the ItemID's from ProductInfo, that when selected populate the corresponding Item# in the Orders table.
I would love if the Quantity(stock) from ProductInfo would automatically fill the Item#CurrentStock field on the form when the ItemID is selected from the ComboBox.But all of the fields only relate to Item# from the first ComboBox!?!?I have tried =dlookup,
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.
I have with my database. It's holds cost data including purchase made in foreign currencies which need to be converted to GBP using the correct exchange rate so a variety of reporting & stats can be performed.I have a table called Costs within which there are 2 fields
Purchase Currency Exchange Rate
I also have another table called Exchange Rates 13/14 within which there are 2 fields
Currency Exchange Rate
When a value is entered in the Purchase Currency field on the Costs table (this is a look up field linked to Exchange Rates 13/14 so it shows the listed currency in drop down) I need the database to automatically populate the Exchange Rate column in Costs i.e. match the value in the Purchase Currency field to the Currency field in Exchange Rate 13/14 and populate with corresponding Exchange Rate from Exchange Rate 13/14.I have tried the following and none work:
SQL Tried
1. UPDATE Costs SET ExchangeRate = [Exchange Rates 13/14].[Exchange Rate] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
2. UPDATE Costs SET ExchangeRate = [Exchange Rate] FROM [Exchange Rates 13/14] INNER JOIN [Exchange Rates 13/14] ON Costs.[Purchase Currency] = [Exchange Rates 13/14].Currency
3. SELECT [Exchange Rate] FROM [Exchange Rates 13/14] WHERE Costs.[Purchase Currency] LIKE [Exchange Rates 13/14].Currency
I know it is possible to have a drop down for Purchase Currency which shows 2 columns (both Currency and Exchange Rate) you can then use the exchange rate figure for a calculated field. The problem I have is that I am importing data into the costs table from excel. In Excel I can only have 1 value in the Purchase Currency column on the upload template. If I just have Euro in this column the database does not match it to the Euro in the Purchase Currency drop down and also store the correct exchange rate.
Or is the alternative to put this into the calculation of GBP Unit Cost where this somehow matches the Purchase Currency in the Costs table to the Currency field in Exchange Rates 13/14 tables and uses the appropriate exchange rate from Exchange Rates 13/14 to calculate GBP Unit Cost in Costs table.
