Lookup Value/Defalut Value Code

Sep 22, 2004

Can anyone tell me how I should modify this code to make it functional for pulling up the previous record's value in the current record? i realize this is wrong, but it's as close as I could get. Do you know how I can fix it so that it will work? I want to put this in the default value field. I think there's something wrong with the Previous Record part. Thanks!


=DLookup ([ERName],Employee Records, [Forms]![ERName] = Previous Record)

KellyJo

View Replies


ADVERTISEMENT

Lookup Value Code Broken... HELP!!

Sep 27, 2004

The following code is being used to pull in the Name from the previous record into the current record. It works GREAT for 28 records, and then it starts just pulling random names from the source table. Can you tell me what I'm doing wrong? Thank you!Private Sub Form_Current()

Dim s As String
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set db = CurrentProject.Connection

s = "SELECT Last(ERName) AS ERName FROM [Staffing]"

Set rs = New ADODB.Recordset
rs.Open s, db, adOpenDynamic, adLockOptimistic
'add to the table

With rs
Me![ERName] = .Fields("ERName")
End With
Set rs = Nothing
Set db = Nothing

End Sub

View 8 Replies View Related

Modules & VBA :: Code For Specific Lookup Selected

Jul 17, 2013

An if statement which will disable out a field depending on which type of field is selected from a separate lookup.I know that is confusing, so let me explain with an example. If I have a lookup field connected to "Vegetables", "Fruits" and "Nuts". If the user selects a "Vegetable" from the lookup field, I need another field disabled, for example sake, "What fruit did you buy?". If the user selected a "Fruit" from the lookup, "What fruit did you buy?" would be enabled.

I know how to enable and disable fields, but it is the If Statement. How would you specify an If statement to specifically look at which category of a lookup is selected?The lookup is not static. So going with our example, the user can add more fruits, vegetables, and nuts as desired, so simply specifying the IDs for the available options will not work. The If statement needs to encompass the category.

View 4 Replies View Related

Forms :: Lookup With Values From Another Table - Populate County Once Zip Code Entered

Mar 22, 2013

I want my form frmintake to lookup with values from another table and populare county once the zip codes has been entered.

I have a table called tblintake and another tblcounty.

tblintake has zip codes entered manually

tblcounty has zip codes and counties for all regions

how does this work out in dlookup.

=DLOOKUP[Zip Code],[tblCounty],[zip Code]=[County])

View 1 Replies View Related

Queries :: VBA Code On Subform To Lookup Email Field On Table And Launch Email?

Jun 26, 2015

VBA Code to go in the on double_click event of a name field in a Subform bound to a table. The subform is just a copy of a data table and within the subform view, When the field "employee name" which contains e.g. John is double clicked, I would like access to Lookup and get John's email in the employee table under field "Email" and launch outlook application and insert it into the To field.

I assume hyperlinking the field can also achieve this similar to what excel does but I am fast learning that what is standard functionality in excel is a whole another story in Access..

View 3 Replies View Related

LookUp Tables For Dynamic Set Of Attributes: Set A Pointer Or Use A Generic LookUp?

Jul 24, 2007

I'm wrestling with the issues; in other threads, it became apparent that because I could not know ahead of time what I will need to know about a given entity, I will use a table to enumerate attributes that is applicable for a given entity.

However, the stumper is that what if an attribute should conform to a set list of values? Since they are dynamic, I would have problem predicting what I will need to be able to lookup, and am even don't know whether I will need a one-many lookup or many-many lookup.

I thought that generic lookup table with a table listing "classes" of lookup would allow me to have one big generic lookup table while using "classes" to act like virtual tables so I can then set the query to appropriate "class" to return just right set of values.

But as I thought about it, I ran into some issues which is pulling me toward the crazy idea that I should have freestanding tables, and use a field in tblAttribute to give me the table's name so I'd know which free-standing table it points to, and have the necessary key to lookup the values within that table.

Even though my gut instincts tell me that I shouldn't be going against the conventions of database design (who the frick goes around creating free-standing lookups?!?), I'm simply not sure how I can use a generic lookup table to hold all information.

For example, suppose I was given a list of values that has its own categories. Since the former design allows only for two level (lookup and lookupclass), where am I to insert that extra level?

Furthermore, I found myself needing a set of virtual keys to reference a certain "class" of lookups for report purposes. That means I need an extra field in my lookup table than I originally anticipates. What if I find myself needing one more field that just won't fit the generic lookup table?

So does anyone have suggestions on how we would create a placeholder for a lookup table that will be made just in time?

View 4 Replies View Related

Filtering Lookup Value Based On Other Lookup Values

Jul 27, 2015

I have three large source tables imported into my database. I have created queries to retrieve relevant values from fields in each source table which feeds into my form. Each field on my form that is connected to the relevant query is a lookup field. For example, one field called "Supplier_Name" another called "Supplier_Code" and a third called "Route_Number".

Needless to say each of my lookup fields are very long. I am trying to filter my search based upon the selection from the previous Lookup field. How I can filter a lookup field's value based upon the previous lookup field selection? Each Supplier has a code and assign route(s) and I have already established these relationships.

View 4 Replies View Related

Lookup Fields Using Lookup Wizard

Mar 2, 2005

Hello,

I wonder if anybody can help me.

I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,

I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.

I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.

Is this possible?

Regards
Nathan

View 1 Replies View Related

Tables :: Lookup Field Using Another Multi Value Lookup Field As Data Source

Nov 23, 2012

how to do a particular thing in Access 2010 (I don't even know if it is possible).

I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")

So I can store, for each different product, none, one, or more options to let the customers choose from.

I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)

The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:

select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]

I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?

View 5 Replies View Related

Please Review This Code, (simple Code) New With Codes

Feb 16, 2006

Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.

Where did I go wrong?

Private Sub Form_Load()
Dim pw As Variant

If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If


End Sub

View 14 Replies View Related

Using Code To Unprotect And Protect Viewing Code

Jan 14, 2007

I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"

Is there a way to write code that will remove that Lock Project check and check it back on?

I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.

Thanks

SHADOW

View 6 Replies View Related

How To Use Lookup

Oct 26, 2005

I have an Invoice & Address file I want to know how I can for example enter customer number in the Invoice file and get the information form Address file in the Invoice file.

Thanks

View 3 Replies View Related

Lookup Help

Feb 19, 2008

Hi all
Im trying to create a database where one field has a lookup to another field in another table (easy enough done) the the next field in the first table has another lookup, however the items the user can select change dependent on what was selected in the first feild eg:
SelectA -A1
-A2
SelectB -B1
-B2
So the user can only select B1 in the second field if SelectB was picked in the first field

Does anyone know whow this is done

Thanks
Chris

View 1 Replies View Related

Lookup Value

Oct 26, 2006

I wonder if anyone can help.
I have 2 tables.
Table1 (lookup table)has 2 fields: 'rate' and a 'minimum amount'. The rate applies to a range of values eg up to 1000,rate=20, up to 5000, rate = 22 etc.
Table 2 has 3 fields: 'UniqueID', 'current_rate', 'amount'.
The 'amount' field in table 2 increases on a monthly basis and when this reaches the next amount threshold (in Table1), the 'current_rate' needs to be updated in table 2 to the appropriate value.
I have searched this forum and Dlookup seems looks to be the only way to do it. However, I think this only returns one result and some of the suggested solutions use a form to input the 'amount', when I have many entries that need to be updated.
Any suggestions would be appreciated.

View 4 Replies View Related

Lookup

Jun 21, 2007

i'm sure this must be pretty simple but when i search for lookup i keep finding stuff about translating names into numeric id's which is not what i mean

what i have is a table TBL_EnergySavings with these fields

IDFuel, IDProperty, IDMeasure, EnergySaving

its a lookup table so that given the three ID numbers (of a type of energy saving measure like loft insulation, installed in a certain type of property which uses a certain type of fuel) we can say how much energy is typically saved in GWhrs

I have another table of installed measures (jobs) which has property type, fuel type and measure type amongst its fields and what i need to do is to fill in a column in that table with the energy saved by each measure

so how do i look up the relavant record from TBL_EnergySavings?

View 9 Replies View Related

Lookup Help

Sep 9, 2005

Hi, im fairly new to access. im creating my 2nd access project for my 2nd year of A levels and i need a bit of help with lookups on a form im using

heres a screenshot of the form

http://beta9.picturehost.co.uk/cat.jpg

What i want is for when you select an option from the category combo box, for the sub category to have limited options. Example: i choose Drinks from the main category combo, the sub category combo will only have a list of types of drinks available.

This has probably been posted before, but to be honest i didnt know what to search for.

Tell me if you need any more information
thanks

View 2 Replies View Related

Lookup Box

Dec 18, 2006

I have a form which is used to enter contact details into my database. This includes the organisation for which each contact works. We have quite a few contacts from each organisation.

To ensure that the organisation name is always entered exactly the same, I'd like to use a lookup box which allows the user to choose the organisation if it's already in the system, or to enter it if it's new and not already there.

Is it possible to have a lookup box which shows the values already entered in the field to which the input will be stored?

Gary

View 2 Replies View Related

Lookup

Mar 2, 2005

I am setting up a form for a database.

It's supposed to be a form for adding a new booking to a table.

Now there's one table with vessels and a different one with lines/deadlines.

Every vessel is on a line that has a certain deadline.

My question: Is it possible for a user to select the vessel from a list on the form, having the form automatically look up the deadline and showing this in a different part of the form.

The meaning is to add a new booking with vessel and deadline into one table...

Don't know if my story is understandble, if not, please ask me to clarify...

Lion85heart

View 11 Replies View Related

Lookup

Feb 16, 2006

I have a user options form linked to a table. The table is just one record with an option group on the form. the user selects which report template they'd like to use. the user closes the form down and resumes normal data entry.

when it comes time to represent the data in a report, i'd like it to display according to the template type the user selected in the options form. I have 3 different report templates. They all say the same thing... one is just prettier than the other!! Users like to customise!!

in effect, i'd like the database to check which report template has been selected from the options table, then open the appropriate report.

It sounds kinda simple but i can't quite figure the code. I think i need to use a lookup statement somewhere.

Any ideas are appreciated.

View 3 Replies View Related

Lookup Box

Jul 6, 2006

I want to put a lookup box on my switchboard where I can type in Company Name and automatically open up the form in the correct Company. I have tried to do this through a query but this just brings up a full-list, also tried a macro but this wouldn't work. Can anyone help please?

View 4 Replies View Related

Lookup

Jul 28, 2006

I would like to create a look up for a field from a table - that displays the OfficeID and the officename when it pops up, but only store the value of the id in the table.

OfficeID officename
1 Office 1
2 Office 2

My lookup Row Source is:

Code:SELECT office.OfficeID, office.officename FROM office ORDER BY office.officename;

Only the OfficeID seems to show in the lookup.

How would I get both to show, but only the OfficeID to be put into the field?

View 1 Replies View Related

Lookup Help

Feb 20, 2008

Hi all
Im trying to create a database where one field has a lookup to another field in another table (easy enough done) the the next field in the first table has another lookup, however the items the user can select change dependent on what was selected in the first feild eg:
SelectA -A1
-A2
SelectB -B1
-B2
So the user can only select B1 in the second field if SelectB was picked in the first field

Does anyone know whow this is done

Thanks
Chris

View 1 Replies View Related

Lookup String

Sep 18, 2005

I have a 67 binary code string produced from a query which concatenates these 1's and 0's.

What I need to do is have access decide what a particular string value/range is and return the process name. e.g

11000000000000000000000000000000000001000010000000 00000000000000001. The process name would be COMP RESOLVED MSA


10100000000000000000000000000000000001000010000000 00000000000000001.The process name would be COMP UNRESOLVED MSA

00000000000000000111010000000110000000000000000000 00000000000000001

The process name would be MSA NEW

There could be about 60 different Process Names

View 1 Replies View Related

How Do Perform Lookup

Mar 10, 2006

Hi i really need help!

i need to be able to use "lookup", i have a set of postcodes from 3000 customers, i need to extract these and compare them to lists i have of postcodes, which make up a region. And run queries from these

e.g a customer on my database has the postcode BD21 7KK

On my list of postcodes i have on paper - WEST YORKSHIRE - BD21 7

therefore The BD21 7KK Customer falls in the West Yorkshire region.

I need to do this four 4 regions with lists i have on paper then run queries.

Can some one please provide the neccessary steps?

Or even give me an example made?

Im really stuck on this at the moment and me job depends on it ! :(

Thanks

View 1 Replies View Related

Lookup Tables

Mar 13, 2006

I have a look-up box in a form that saves the description selected into a field in a query. I then use that query to produce a report, but on the report, the query has stored the ID field so the report doesn't show the description the user selected - just a number. How do I get the field in the query to save the description rather than the ID field so I can show it on a report?

View 5 Replies View Related

Lookup Alarm

Sep 4, 2006

I am using Access 2003 and I am trying to get my database to lookup required information as soon as access starts. If the information is lower than a certain figure I want a macro to run to present a warning on the screen stating that the required item needs attention.
CAN THIS BE DONE?

Regards

View 3 Replies View Related







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