Reference Another Table

Oct 18, 2004

I'm trying to achieve something that I'm sure someone has figured out long ago. I have a DB that currently only shows the zip code for certain records. Is there a way that I can have the DB take the current zipcode, reference a zip code table, and then return both the zip and city/state?

Thanks

View Replies


ADVERTISEMENT

Autofill Data To A Main Table From A Reference Table

Mar 19, 2007

First of all apologies for the lack of proper terminology I'm a novice Ms Access user and I like to thank everyone in advance for trying to help.

Ok here is the situation:

I have two tables, NewJobs and Contacts which have the following fields.

Newjobs
--------
JobID (AutoNumber, Primary Key)
JobName
JobDate
JobDescription
JobOwner (Linked to table 'contacts' via LookUp)

Contacts
---------
DisplayName
EmailAddress
Department
Extension

Ok basically what I want is to have a form based on table NewJobs which will allow me to enter new jobs into the database. When I get to JobOwner a drop down list linked to 'Contacts' table will show me all the data from column 'DisplayName' and allow me to select it (saves time on typing). I have already done this and its not a problem.

Now I would also like in the same form to have additional fields from table 'contacts' such as EmailAddress, Department and Extension which will autofill with the right information soon after I select a JobOwner from the drop down list.

So for example if I select 'Joe Bloggs' Access will automatically fill the additional fields in the form with Joe's information (department, extension etc) from the Contacts table.

I hope all this makes sense. Thank you all for your support.

- Mitch

View 3 Replies View Related

Dcount To Reference A Different Table?

May 10, 2005

I'm trying to get a query to count all the pole positions, fastest laps, first places etc a driver has had, here is a picture of the sample for fastest lap, i have a small problem i cant get it to only count the current drivers fastest laps

so i need to know how i get the drivers.id to match with the driver id in the results table


http://www.alexsawczuk.co.uk/access.jpg

basically i want to know how i get the dcount to reference a different table?

View 1 Replies View Related

Query Calculates Value Need To Reference In Another Table

Feb 1, 2007

I have a query that currently calculates a rating based on various values entered into the database. This rating corresponds to a Quality Index value based in another table. I tried to set up a query that links the rating value in the query to the Rating in the table, however, when I run this query, I don't get all the results I should. Is there some other way to make this work? I thought it would be simple to link the rating value that is calculated to the rating in the table and have it report the Quality Index value associated with that value. Please help! :confused:

View 5 Replies View Related

Modules & VBA :: Reference To A Table On Subform

Oct 22, 2013

I have a form which have a sub form. In the sub form I have a the direct table to update data on it.

Example:

The form has the name of the project and the subform the name of one of the items of the same project and the table present the documents for the particular item.

The users have the option to add or edir docs to this item.

If I would like to reffer to a value they are entering on this table in VBA . how can I do that?

(I would like to present a msgbox when they are entering a date that isn't right on the table for this item.)

By the way, I can't use validation rule because I have a different dates needed per item.

View 2 Replies View Related

Tables :: Look Up Reference For Table Field

Dec 5, 2013

When entering a lookup reference to a table field it pop up message saying, "you can't modify the table 1 this time as it is in use by other person or process" i have closed all forms and table reports but the problem is same to rectify ? also when going to see the realationship then there shows no relation ship developed even after the lookup reference from one table filed to another table field.

View 1 Replies View Related

Tables :: Reference A Table Field

Apr 10, 2013

I have two tables, Table 1 and Table 2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data. I know that if both tables are open I can reference the fields on Frm1 and Frm2 with an IF statement: If Active = True And Forms!Frm1a.DoNotchange = False Then..And I know on the update query I can reference a control on a form by Forms!Formname.Control. Wll works fine.What I would like to know is can I reference a table field in the same way. For example, I have tried to use the IF Statement If Active = True And Tables!Table1.DoNotchange = False Then..Is there a way to reference the Table instead of the Form in both the VBA and the query? The problem seems to be that I have the two bound controls [Active] on Frm2 and [DoNotChange] on Frm1. (Which I need).

View 5 Replies View Related

Tree View Library Reference Table

Nov 7, 2005

I am not sure if this is the correct forum to post this question but I am trying to create a tree view library reference database for several pdf documents I need to index, and would be happy if there is a sample available to download.

Thanks!

View 1 Replies View Related

Tree View Library Reference Table

Nov 7, 2005

Tree view library reference table

I am not sure if this is the correct forum to post this question but I am trying to create a tree view library reference database for several pdf documents I need to index, and would be happy if there is a sample available to download.

Thanks.

View 1 Replies View Related

Update Field Based On A Reference Table

Mar 15, 2006

I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.

The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].

tblReference example of field values:

Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset

So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.

Any help is greatly appreciated.



Thanks,

View 7 Replies View Related

Macro To Fetch Recordset Corresponding To Reference Value From Table

Mar 3, 2014

I reused one macro to develop a form in MS access.

The macro fetch the record set corresponding to the reference value from the table.

The reformat value is a numeric field.

The macro code is :

Private Sub Modifiable20_AfterUpdate()
Dim rs As Object
Set rs =Me.Recordset.Clone
rs.FindFirst "[Reference_Number] = ' " & Me![Modifiable20] & " ' "
If Not rs.EOF then Me.Bookmark = rs.Bookmark
End Sub

What to change in the above code to make it work....

View 3 Replies View Related

How To Link Table And Give Reference Of Column

Sep 10, 2015

I have created two tables, I want to copy one column from one table to another table column with repeat value in rows.

example :- copy "Material number" column from Table1 to Table 2. In Table 2 Material number will repeat in 20 rows.
Like in excel we give one cell reference to multiple rows.

How I can link table and give reference of column.

View 1 Replies View Related

Cannot Reference A Table With Multivalued Field Using IN Clause

Jul 3, 2014

I have an access db that is linked to a sharepoint list (the list originated in Access).I tried a simple select query with parameters to search for by id and another query of the same table to search by date.They both come back with the same error of "cannot reference a table with a multivalued field using an IN clause that refers to another database"Iv'e gone through the table and these fields that the query is using and none seem to have any multivalues.

View 6 Replies View Related

Using Variables In A Form To Reference Table Field Names

May 24, 2006

I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record.
I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:

Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.

'------Code I'm Currently Working On-------
Dim intX As Integer
Dim EventVal As Integer
Dim strX As String

intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal <> intX And intX < 6
intX = intX + 1
Loop

strX = str(intX)
Dim E_Name As String
Event_Name = [Event strX ]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]

'-----Old Code That I'm trying to simplify-------
If Event_List = "Event 1" Then
Event_Name = [Event 1]
PMT = [PMT 1]
Receipt = [Receipt 1]
Payment_Type = [Payment Type 1]
PIF = [Paid In Full 1]
Camp_Site = [Camp Site 1]
Cabin_Number = [Cabin Number 1]
ElseIf Event_List = "Event 2" Then
Event_Name = [Event 2]
PMT = [PMT 2]
Receipt = [Receipt 2]
Payment_Type = [Payment Type 2]
PIF = [Paid In Full 2]
Camp_Site = [Camp Site 2]
Cabin_Number = [Cabin Number 2]
ElseIf Event_List = "Event 3" Then
Event_Name = [Event 3]
PMT = [PMT 3]
Receipt = [Receipt 3]
Payment_Type = [Payment Type 3]
PIF = [Paid In Full 3]
Camp_Site = [Camp Site 3]
Cabin_Number = [Cabin Number 3]

PLEASE HELP!!!!

View 3 Replies View Related

Forms :: Possible To Store Colors In A Form Or Table And Then Reference Them While In VBA

Dec 5, 2014

I am trying to see if it is possible to store colors in a form or table and then reference them while in VBA. What I am hoping to do is when I write all my code for command buttons to change On Got Focus, instead of writing xxx.backcolor = RGB (255,255,255) i could do something like xxx.backcolor = Forms!HiddenColors!Command That way if i want to ever change the color scheme of the db, I can change it in one place rather than hunt lines of code.

I have played around but with no success. Technewonline is a website that specializes in introducing the latest technologies such as Best Tablet Android Have Price Under $200 and Best tablet of Apple in 2014 and The Best Midrange Smart Phone In 2014 and Top Best Ultrabook Of 2014 and The Best Phones 4G Valued At Under 300 USD is also a website for sharing your tips about computers, mobile phones and tablets, products are available from leading supermarkets will surely satisfy you.

View 1 Replies View Related

Can Excel Spreadsheet Reference Access Table For Its Data

Mar 27, 2015

Can an Excel spreadsheet reference an Access Table for it's data? Sort of like a vlookup, but instead of referencing another spreadsheet, I'd like to pull data in from a database.

View 1 Replies View Related

Forms :: Display Field From Separate Table In A Form To Use As Reference

Mar 25, 2014

In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.

So for example

112 - Main Road

If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.

Can i do this? If So how?

View 5 Replies View Related

Forms :: Reference Table Being Updated When Launch Edit Routine

May 18, 2015

Currently running Access 2010.

I have a small database where we enter audit log data. In my add add screen from the switchboard, one of the fields is the user the audit is assigned to. So that our group wouldn't be typing their names a bunch of different ways I created a reference table called "Ref Data 005 - Contact Name". So i pre-populate the Contact Names such that they will be more consistent.

The add works perfectly. I built all my screens using the forms wizards.

Now for the modify routine. If the user wants to go in an modify one of the other data elements, I force them in the wizard to do this modify by choosing thier name. Therefore, when i built this screen using the wizard I reference the "ref table" mentioned above. I then throw open another form displaying all of the fields based on the records that it finds for that user when they choose the name from the Ref data 005 table. Hope that makes sense. Ie.. they choose the records they want to possibly modify by first choosing their name from the ref table.

It correctly pulls the records for whatever user you choose. However, when you make the update and save, it is changing the Ref table name as well.

So for example if three users were in my ref as :
bill
joe
john

And you launch the form, lets say joe shows up in the list, when i change it to bill to retrieve bill's records , it will do that but when i make the change, then Joe is changed to bill in the reference table.

View 4 Replies View Related

Queries :: Reference (parent) Description In Hierarchical Data Table

Apr 30, 2015

I have a table I'm trying to query information out of. Key fields are below:

RecNo (Key)
ParentRecNo
Description

I need to have a "record" in the query show both the description of itself, as well as it's parent. I was hoping to use Dlookup directly in the query design. If there is no other way, I suppose I could create a temp table and look through the recordset in VBA and dump them into the temp table, but like I said; quick and dirty was the hope here, it's for my own use, and the tables aren't large 50-100 records is what I'll be pulling out when I use this.

View 1 Replies View Related

Reference

Feb 24, 2006

Please help, how can I refer to the field of a subform in Visual Basic when the (main) form is open?

A)
Private Sub supplierID_AfterUpdate()
Forms![invoices]![KombináltLista4].Requery
End Sub

B)
Private Sub supplierID_AfterUpdate()
Forms![details1 Segédűrlap]![KombináltLista4].Requery
End Sub

These don't work. (The main form: INVOICES; the subform:DETAILS1 Segédűrlap; KombináltLista4 is the name of a combo box)

The version B work, when I open the form and the subform together, but of course I'd like to work only on the form (where the subform is displayed as well, but I can't refer to it...)

THX

View 3 Replies View Related

How To Fix A Reference

Feb 24, 2005

I created a new database and i am getting an error.

Your ILFdatabase database or project contains a missing or broken reference to the file 'SearchCtrl.ocx' Version 1.0

Can anyone help?

View 1 Replies View Related

Change A Reference

Apr 29, 2005

I need to change the reference "Microsoft Access 9.0 Object Library" in Access 2000 with the "Microsoft Access 11.0 Object Library" but I can't de-select it 'cause it's used by the program. Another problem is that I tried to import the 11.0 copying the file and when I select it from the panel control it seems to import without problems, but when I search it in the list, the 11.0 doesn't exist.
How can I import it?

View 2 Replies View Related

Best Access 03 Reference?

Nov 1, 2005

Heya,

In your opinion, what is the best Microsoft Access 03 Reference book for learning?

Mateo

View 2 Replies View Related

Reference Problem

Jan 12, 2006

Hi, I have been designing a database on my computer which seems to be working fine; 2 more users in the company should be able to use the database.In order to see if everything was working properly on other computers, I have put a copy of my db on the server and tried to open it on the other users computer station. Unfortunately, the reference library that I have been updating during the design of the database doesn't seem to apply to the other computers. I have been trying to update it on the other machine but the references on the tool menu in open module is not available.
I didn't expect this to hapened and I am not sure what to do, what would be the apropriate method to make the library available to the other computers?

View 4 Replies View Related

Reference Calendar

Aug 19, 2006

Is there a way to insert a calendar on a form from which a user can select a Month year and day similar to using the calendar on Outlook or Lotus Notes to set up or view appointments or meetings?

View 5 Replies View Related

Reference Problem In An MDE.

Apr 19, 2007

Hi,
I have created an mde that was installed on more than 30 computers. All of them work fine but one who gets a message saying that there is a broken or missing reference. How can I repair this knowing that I cannot access the references in a mde?
Thanks!

View 3 Replies View Related







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