Forms :: Matching Values Of Fields With Data In The Table

Oct 20, 2014

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

View Replies


ADVERTISEMENT

Forms :: Search Multiple Fields In Table And Open Matching Records For Editing

Dec 10, 2014

Create form to search multiple fields in table

Return records that match search

Open the record that you want in Form View for editing

View 1 Replies View Related

Compare Data In Different Tables And List Non Matching Values

Nov 2, 2007

Hi

I am trying to compare data in two different tables and list the data that does not match. Not sure if this is possible. Details below

Table 1

xyz - A1 - Do not use
ABC - A4 - Use

Table 2

123 - A1

The result of the query should show me

A4 (as this does not appear in table 2

I am not too familiar with Null values as there will be many null values that i want to ignore.

Any help will be appreciated.

View 4 Replies View Related

Append Data To Field For Matching Fields

May 18, 2005

Hello,

I have a few tables containing different exam data for students - and a unique identifier with each. I need to add another unique identifier (which is already specified from another system).

Now, can I use the existing identifier (UPN) to automatically put in the new identifier into a new field (AdNo).

If that makes any sense at all, I will be surprised...thank you!

View 1 Replies View Related

General :: Merge Two Fields From Two Databases That Have Matching Two Data Ranges

Mar 25, 2014

I have two databases (files). Each file has a format like the one below.

File 1:
chr start end
chr1 1000 2000
chr2 3000 4000

File 2:
chr start end
chr1 1500 3000
chr2 3750 4000

I want to be able to merge the two records together if the field chr matches between the two files plus if there is an overlap between the start number and end number from each file. For instance the first record from each file would match because the range from 1000 to 2000 of file 1 has numbers consisting of 500 numbers (1500-2000) that are also present and overlap in file 2 (1500-3000). I possibly cannot use < or > since the ranges from each file will vary to different degrees. Perhaps there is a between function that might work...

View 7 Replies View Related

Modules & VBA :: Completing Fields On A Form With Matching Data From Previous Records

Jun 4, 2015

I'm trying to make it a little further with my new call answering database at work.

Basically it consists of a main form that has a button leading to a call answering form for each business that we represent. it also has a text box that displays the number of the caller when the telephone picks up, the correct call answering screen is also popped by the telephone software and a macro whenever the phone is answered.

I've got code in place that automatically puts the date and time in the correct field and also copy the incoming telephone number from the main form into the caller number field on the call answering form.

I'd like to take this automation a little further now and get the form to autopopulate the caller details if that caller has called before (we get a lot of calls from the same people) so i'd like to make the form search the table it's linked to for the incoming phone number and to fill in the name, email, company etc... for the caller according to the previous record.

The code for the 2 forms i've currently got setup (the switchboard and one call answering screen) are as follows

Switchboard:-

Code:
Option Compare Database
Dim WithEvents MaxxCom As Metro_MaxxCom_CTI_COM_API.CTI
Private Sub cmd_onnet_Click()
DoCmd.OpenForm FormName:="On_Net_Communications"
End Sub

[Code] .....

View 8 Replies View Related

Command Button To Open Another Form - Matching Specific Data Fields

Aug 25, 2011

I have a subform in which I want to put in a Command Button to open another form.

I used the wizard as per normal, but when you come to matching specific data fields, there is nothing in my left hand column of the sub form. I have checked the record source property and it is bound to the correct table.

I have read that this is an issue with Access 2007? Is this the case or am I missing something obvious here?

View 3 Replies View Related

Queries :: Show All Fields With No Matching Records - One Table

Aug 14, 2013

How to get the following results using 1 table:

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.

View 5 Replies View Related

Forms :: Matching ID Fields To Auto Fill Form Field

Aug 30, 2013

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,

View 3 Replies View Related

Forms :: Newly Added Values In Combo Box - Streamline Data Entry With Auto-populated Fields

Jun 24, 2014

I have a combo box that pulls account name data from tblAcctInfo. the combo box has an OnChange event which updates a textbox, txtAcctAddr.

when i have a new account that i would like to be listed in the combo box, i use a form, frmAcctAdd, to add a record to tblAcctInfo.

what i would like to do, is:
1) when i type a new value in the combo box that isn't in the list, have that string value pre-populate in the frmAcctAdd.
2) when i have added the new account info into frmAcctAdd and then saved the record, i would like the new value to pre-populate in the combo box, with the txtAcctAddr textbox also updated via the OnChange event (or maybe a different event is more appropriate?).

I have created a long version of this which requires a lot more user interaction (1-user typing in a new value into the combo box, 2-user RE-typing the SAME value into a data entry form, frmAcctAdd, 3-user saving the new record, 4-user re-clicking the combo box and selecting the newly added value) but i am trying to streamline the data entry with auto-populated fields.

View 6 Replies View Related

Forms :: Save Values Entered Into Unbound Text Boxes To Fields In Table

Mar 6, 2013

how can i to save the values entered into unbound text boxes to fields in table

View 2 Replies View Related

Modules & VBA :: Data Matching Between Table And Text File

Feb 6, 2015

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

View 9 Replies View Related

Tables :: Auto-populate By Matching Data To Another Table And Selecting Corresponding Field

Sep 16, 2013

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.

View 4 Replies View Related

Forms :: Method Or Data Member Not Found When Inserting Values To A Table

May 13, 2013

I am getting method of data member not found for the following code when inserting values to a table. Form has three frames. Add/Insert is on OnClick Event.

MsgBox "First Value, " & Me.txt_FSNo
CurrentDb.Execute "INSERT INTO BackchargeLog (BC_FSNo, BC_FContract, BC_FPurchaseOrder, BC_FProjectArea, " & _
" BC_FTitle, BC_FDetailDescription, BC_FSupplierToBeCharged, BC_FSupToBeChargedNumber, BC_FSTBCContactName, BC_FSTBCContactNumber, " & _
" BC_FSTBCContactEmail, BC_FIncidentReportedDate, BC_FInitiatedDate, BC_FEstimateAvailable,

[Code] .....

View 7 Replies View Related

Display A Record Once If Data In Two Fields Have Matching Record

Nov 4, 2005

Hello eveyone . . .


i am attempting to create a VERY complicated query . . . it's sort of confusing:

I recieve data from an outside source and it is up to us to format it for our own analysis . . .
i have fields:

MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc

The fields we want to focus on is "AccNo" and "TestCode"

Now i want to filter based on this condition:

If "AccNo" AND "TestCode" BOTH have duplicate data in a record. Show that record only once. Remember. . . "AccNo and TestCode, both these fields have to have duplicate data in a record.

Example:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
CAPT.....M566679.....John Blue.....123456.....CBCA
CAPT.....M566679.....John Blue.....123456.....CBCA
the example above should only display once because of the repeated AccNo and TestCode

Example:
MethodCode..... AccNo .....PatientName.....MR..... TestCode ..... etc, etc
CAPT.....M566679.....John Blue.....123456..... CBCA
CAPT.....M566679.....John Blue.....123456..... LIPID
the example above is just fine because the TestCode is not matching

How do i create a query to accomplish this? someone please help, i've had troube with this for the passed couple weeks . .. .

View 2 Replies View Related

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:

Compliance Test 01:

-----|Sample1|Sample2|Sample3|etc...
Item1|GradeA |GradeB |GradeC |etc...
Item2|GradeB |GradeA |GradeC |etc...
Item3|GradeC |GradeC |GradeA |etc...
etc...

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.

View 1 Replies View Related

Forms :: Populating Data From A Table With Multiple Fields?

Sep 25, 2013

I am trying to create the form so when the first field (Start Date) has a date selected, it limits the following field (Report Date) to the matching available data, and same with the third field (Production Division) on the previous two.

I also need the form to allow new dates / production division combinations, but not duplicate.

I starting working with a cascade function but got confused and not sure if that is where I should be headed.

I have attached 2 different formats of the same sample data.

View 13 Replies View Related

Forms :: Allowing Data Entry For Unbound Fields (not In Table)

May 1, 2013

I'm trying to build a form based on a table where the user can also enter data to update the table but with data fields not originally in the table. Below is a summary of what I have and what I need:

1.) A file that will be uploaded daily into a Table (ie name Denials extract table).
2.) A form bound to table Denials extract
3.) Additional fields that will be updated by users that is not included in this table b/c they are not available in the system we are getting the data extract from.
4.) The user will look up a key field that will allow them to review it in our system. Once they "work" the record, there will be multiple fields that they will update with this information that are not in the bound recordsource (table Denials extract).

Do I create another table with these fields even though they will be blank at first and make them a subform that will have a "primary key field" that will link the main form with the subform? Will they need to "update" a whole new table? Not really sure how that would work since they will be updating fields not in the bound table.

View 8 Replies View Related

Forms :: Auto Populate Fields And Save Data To Table

Dec 16, 2013

In my UpdateForm I have 3 fields. PartNumber, Description and SerialNumber. I use 2 tables for these. My MainTable and PartsList Table.

In my PartsList table I have the list of PartNumber in Column (0) and Description in column (1).

In my MainTable I have 4 fields: TransactionID (autoNumber), PartNumber(text), Description(text) and SerialNumber (text).

Now, In my UpdateForm I want the user to just select the PartNumber with a combo box (that also show the "Description" (I created this using the combo box wizard)). But I want to auto populate the field in my "Description" text box every time the user will enter new record and will also update my MainTable with all the values they entered in my UpdateForm.

I tried this codes in the after update of PartNumber combo box (properties):

Description = Partnumber.Column(1)

I tried also:

me.Description.value=me.PartNumber.column(1)

both codes unsuccessful.

View 3 Replies View Related

Matching Values From 2 Tables

May 4, 2007

I have three tables: tblProducts1, tblProducts2 and tblProductSales.tblProducts1Code CostABC 20BVC 35ABC 30tblProducts2Code CostABC 10BVC 55ABC 20tblProductSalesCode RevABC 70BVC 25ABC 20BVC 15DCC 33I want to produce a query that looks like this:Code Rev Cost ProfitABC 90 80 10BVC 40 90 -50DCC 33 0 33How can I do this?Thanks,Jon

View 1 Replies View Related

Matching ID Values Between Two Tables

Jun 4, 2014

I have two tables, one has two fields:student ID and student name.its kind of like this:

1 Mark
2 Tom
3 Franklin

the other table has three fields: student name, student classes, it goes like this:

Mark calculus
Mark Biology
Tom Statistics

Franklin Calculus
Tom Chemistry

what I want is for the second table to have its related id from the first table so it could be like this:

1 calculus
1 biology
2 statistics
3 calculus
2 chemistry

I cant simply make find and replace because the records are a lot is there another way should I relate the tables of something how will it work?

View 8 Replies View Related

Number Of Values Matching Criteria Across A Row

Feb 1, 2007

Dear all:

I can't seem to figure this one out on my own; I'm trying to create a query that counts the number of values in fields across a row that match certain criteria. For example, the data I'm querying looks like:


ID 01-1 01-2 02-1 02-2 03-1 03-2 04-1 04-2
1 85 76 68 72 67 63 74 73
2 32 34 70 65 67 80 68 70


and I need to find out, for example, how many numbers for each id are greater than or equal to 70 but less than 80. I've tried using DCount, but it gives me the number overall that match the criteria instead of the number per row (so I'm sure I'm doing it wrong). I've tried using a crosstab query, but I can't suss out how they actually work when you've got more than one column of data that you need to work with unless I put ID as column headers and the other fields as row headers... that would make my query about 1500 columns wide and 10 rows long, though, which just seems wrong... ?

In a slightly different approach, I've done another query on the data that makes it look like:


ID Number First Second
1 01 85 76
1 02 68 72
1 03 67 63
1 04 74 73
2 01 32 34
2 02 70 65
2 03 67 80
2 04 68 70


But again, I keep ending up with silly answers (like the count for the entire set of data, rather than just per ID). I'm *sure* I'm just not quite getting how DCount works... and it's possible I'm trying to use it for something for which it wasn't designed. Any alternative examples would be most helpful.

I'm not sure how to compress the data any further. I could, technically, get it down to two columns (ID and <number to query>), but the only way I can see of doing it is to create a temporary table for the first column and append the second column's data to it, which I'd have to do every time any of the numbers changed.

I would prefer to use the first approach, as that query is useful in other calculations and the second is a bit of a faff (though not too bad, just a union query). Anyway, I'm just stuck and instead of continually banging my head against the wall, I thought I'd ask you kind folk for help. :)

-Jen

View 7 Replies View Related

Queries :: Matching Values Without Duplicates

Oct 23, 2013

I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:

Part ID ---- Profile Profile ID ---- Locations
Part1 - Profile 1 Profile 1 ---- Location 1
Part2 - Profile 1 Profile 1 ---- Location 2
Part3 - Profile 1 Profile 1 ---- Location 3

I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.

Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?

View 8 Replies View Related

Forms :: DLookup - Using Two Fields (number And Drop Down Text) To Query Table For Data

May 29, 2015

DLookup function. (this is for a stamp collection database).

On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.

I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.

I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.

That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")

So it will populate the "StampName" data to match the "Catalog" number entry just fine.

However, I need to add a second layer to incorporate the Country.

Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".

I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :

=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")

Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.

Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.

View 4 Replies View Related

Matching Fields

Jun 19, 2007

Hi All,

Wondering if you can help I have a table called "example" which has field "a" "b" "c" "d"...

I then create another table called "importtable" with field "a" (imported in from Excel)...

I would then like to create a query which matches any and shows all the records which I imported in from Excel to my current table called "example" I looked at joining via "relationships" using a Select Query but it doesn't quite show what I'm after...

Should add I would like Field "a" in both tables to show but only if the number exists in the "importtable" if not then don't show..

Any tips :confused:

View 1 Replies View Related

Matching Two Fields In Two Tables

Nov 13, 2006

In a database I have two tables, one is linked to an excel sheet (our customers order) and the other is created via a "make-table query" from our business system.

In both tables I have the customers part numbers and neither contain a primary key.

What I need to do is compare the part numbers in both tables to find if a part number is present in the order but not in our business system.

I just cannot figure out how to do this.

Any ideas are greatly appreciated

/twallstr

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved