Lookup Associated Fields With CourseNumber Selected From Combobox

Sep 15, 2011

I am new to access. I have the following code to lookup associated fields with a CourseNumber that is selected from a combobox. The information is then stored in a CourseEnrollment table linked to student info.

The CourseNumber and associated fields are located in a LU_CoursesOffered table. The LU_Courses Offered table has been populated by choosing associated data from combo boxes based on other LU tables.

I am getting the primary keys returned for the associated data rather than the text stored with that key.

Private Sub Combo103_AfterUpdate()
CourseName = DLookup("CourseName", "LU_CoursesOffered", "CollegCourseNum = " & Nz([CollegeCourseNum], 0))
LevelSection = DLookup("LevelSection", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
AcadGrad = DLookup("AcadGrad", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
Instructor = DLookup("Instructor", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
End Sub

View Replies


ADVERTISEMENT

Forms :: Update Row-source Of Combobox Based On Value Selected On Another ComboBox?

Apr 26, 2015

I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.

Code:

Private Sub Manufacturer_AfterUpdate()
If (Me.Manufacturer.Value = "Siemens") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SeimensTable"
Me.Model.RowSource = "SELECT Model FROM SeimensTable"
Else
If (Me.Manufacturer.Value = "Samsung") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SamsungTable"
Me.Model.RowSource = "SELECT Model FROM SamsungTable"
End If
End If
End Sub

But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?

View 5 Replies View Related

Combobox.selected

Mar 1, 2007

Greetings all, am in the middle of learning vb.net and have just been thrown in the deep end of Access as a concequence lol.

There is more to it, but the core of the problem is this:

imagine a form - on this form is a combobox and a button

the combobox displays the result of a query in its drop down section, and once a record is selected it is displayed in the text area of the box - no problems so far

the button has a function by which an additional record (the currently displayed one) is moved into the afore mentioned combo box from another. again this works just fine.

what i want to do is (i think) query the result of the query on the combobox to find the record within that query which matches the currently displayed record and then set that result = combobox.selected

oh and to make matters worse i have no idea how to write a query from scratch in VB wthout use of a wizard <sigh>

help anyone?

View 1 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

The Selected Value Displayed In A Combobox

Apr 29, 2005

I'm sure this is simple and I'm just missing something...

I have a combo with two columns and the row source set up as a query which returns two columns.

When I click the drop-down arrow for the combo box, I get exactly what I expect.

What I want to do is initialize the combo box so that the "default" selection is displayed in the "text" or "value" section of the box. I also want to be able to update this when the user makes a selection.

I have no problems when using a list box with something like:
listbox.value = <Some Index in the listbox>

With the combo box, I've tried:
combobox.text = xxx
combobox.value = xxx
combobox.column = xxx
combobox.itemdata = xxx
combobox.listindex = xxx

The value property sort of works -- It displays the data from the first column right justified in the "text" section of the combo box. I need to have both columns displayed the way they are when the combobox is dropped down.

Any thoughts?

Thanks in advance...

View 3 Replies View Related

Forms :: Combobox With Default Selected Value?

Nov 1, 2014

i have 2 tables (clients, frequencies) where in table "clients" i have a column to store ID of "frequencies" table (id, description).

in my form "FClient" i display data stored in table "clients" and i have a combobox that should display the "frequencies" descriptions, but with a frequency selected by default...in fact the one that has its ID stored in table "clients". i'm able to display all frequencies descriptions but i don't know how to easily select and then display, as default, the frequency stored in table "client"

View 3 Replies View Related

Record Lookup In Form Flashes One Or More Records Before Displaying The One Selected

Dec 3, 2014

I have a form (Student Details) that has an unbound lookup box using a query to select a student. The lookup has the hidden Student ID autonumber key field plus Class Number, Last Name, First Name, Status, sorted by Class Number (descending), Last Name, First Name.

The form record source is a similar but unsorted query.

There are nearly 1000 student records. The form has pages (tabs). The lookup box is in the form header. There are dozens of fields in the 1st Page (General) including an picture (attachment, not hyperlink) field holding a photo of the student, the other 6 pages have less.

When you select a student via the look up, at least one sometimes more students "flash" before it displays the one you want, which seems to display twice before it stops.

I deleted the picture field to see what difference that made, because it seemed to do the most flashing, and indeed, without that field you go right to the record. I looked at the Student table indexes and it seems that field type is not index-able, which is understandable.

Is there any way to improve this with still leaving the photos as attachments? Some thoughts:

It improves if it is displayed first as an Icon or Paper Clip, and probably would if a hyperlink field, but we like to have the picture displayed and not have to open the link to see it nor worry about the location changing invalidating a link. It seems that if the record was displayed with the picture field only filled after all the other fields were filled it would make for a better impression.

Can an On Load form event VBA or Macro be written that either hold the picture back until the rest of the fields are displayed or if an icon or changed to a hyperlink to open. Would it work if the pictures were attached to a separate joined table?

View 11 Replies View Related

Forms :: Remove Item After Being Selected From Combobox?

Mar 30, 2015

How can I remove an item after being selected from a combobox ?

View 2 Replies View Related

Forms :: Fill Value In Textbox When Item From Combobox Is Selected

May 19, 2013

I have a table, Registration, with 8 fields:

inschrijvingsid,name,gemeente,gsm,telefoon,email,a ankomst,gerecht,personen

I have another table, Login with 3 fields : Naamid,name,,status

Then I have a combox that is connected with fldname from the table Login.

In that form I have 8 textboxes:

name,
gemeente,
gsm,
telefoon,
email,
aankomst,
gerecht,
personen

And status.

Now what I can't. I want when I select a record from tblLogin Like for example Daan that I see in the textboxes his info from the tableRegistration.

Also when I change the textbox Status it and click "Verander status" it changes in the database.

View 3 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

Forms :: Combobox To Lookup Any Part Of Field

Jun 6, 2013

I am using a combobox to lookup contact names.

The rowsource is taken from a field in a contact table that contains the contact's First name and then Last name.
You could use the dropdown to pick the name or type the First name into the combobox.

The problem is that the combobox matches only the beginning part of the field as you type. So if you start typing the person's last name, the combobox would not display anything.

Is there an easy way to allow the combobox to lookup any part of the field that matches the text being typed?

View 13 Replies View Related

Forms :: Split Form Combobox Requery Based On Row Selected

Oct 2, 2013

I have a split form that was not made by wizard. On the form part I have a combobox that is unbound to the form data set. The combobox has a query row source that is based on the current row selected. I want the combobox to have an up-to-date result based on which row is selected.

If I set the combobox to requery in the form_current event then I get what I want. I don't want to put up with the little delay that is generated every time a user changes rows because of the requery, though. I only want the requery to happen when they use the drop-down menu.

I have the requery in the gotfocus event of the combobox on the form. I mostly get what I want this way, however if they select an item in the drop-down list, then choose a new record in the datasheet, then try to use the combobox again, the combobox is not refreshed (because it never lost focus?).

To get around this, I've tried to setfocus to something on the form in the on_current event, but access gives me an error: 'Access can't move focus to the control btn_Refresh'.How can I get the combo to requery only when users are about to use it?

View 4 Replies View Related

Forms :: Deleting ComboBox Lookup Options But Keep Old Records

May 14, 2014

I have a ComboBox which looks up the records in a query. The query contains the names of current employees. The table upon which the query is based lists Employees and their start date and finish date. The Query is based on finish date being Null (ie they're still working here).

In the main form which details tasks, I would like the ComboBox to just list current employees (which it does as it's based on the query) but when an employee leaves, their name is deleted from all previous records.

Is there a way to keep the ComboBox options limited to current staff, while also keeping the names of previous employees in the form?

I checked the table which the form is based on and it still has the values, so I'd like to show in the form too as that's what people will be working on.

View 1 Replies View Related

Tables :: Requery Combobox Lookup On Table Datasheet

Feb 20, 2014

When designing a table I've created a field and set its lookup properties to display a combobox with a row source that returns a DISTINCT set of values already entered into the field.

After a row insert or row update the combobox needs to be required to ensure its list is complete.

If I create a form to display my datasheet this is easy. But I'd prefer to enter data directly into the table datasheet directly. I need to enter simple data into about 20 different tables and I'd prefer not to create 20 forms unless it's really necessary.

The lookup wizard generated entries similar to those I'd previously created manually, except the wizard generated a couple of extra settings that appeared briefly that were not part of the regular set of lookup tab properties. These additional settings referred to 'update propagation'. Once they'd disappeared I couldn't see any way to get them back..

(I am using Access 2013)

View 3 Replies View Related

Forms :: How To Link Combobox Between Form And Subform To Only Show Selected Filtered Data

Apr 1, 2013

I have a suppliers table and a products table. Two forms, Stock form with combobox to list Suppliers and a sub form with combobox that lists Products.I want to select a supplier from the Stock form and then the combobox in the subform to only list products directly sold by the Supplier.Have dabbled in SQL as follows:

SELECT Products.ProductID, Products.ProductName
FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID
WHERE ((Stocksubform.SupplierID=Stock.SupplierID))
ORDER BY Products.ProductName;

View 2 Replies View Related

Modules & VBA :: Wildcard Search Of Combobox - Lookup Values In Table Column

Nov 19, 2014

I have a form which has a combobox called Task_Ref which looks up values in a table column.

I would like to be able to set the tickbox value of tickbox called P1 to True if the combobox contains the word "test", each entry on the combobox selection may vary such as:-

Test number 1
Yesterdays Test

As long as the word "Test" appears I would like the above to happen?

I was thinking of something along the lines of:-

If InStr(Task_Ref.Value, "Test") > 0 Then
P1.Value = True
Else
P1.Value = False
End If
End Sub

But this hasn't worked

View 4 Replies View Related

General :: Lookup Combobox - Field Show Number Instead Of Text In Listbox

Jul 7, 2014

I have a field in a table where it is a lookup is a combo box. So this makes the data type of the field a number when in reality it is a text. When I have this field show in a list box it shows the number instead of the text.

View 5 Replies View Related

Reports :: Print Or Preview Reports Based On Selected Value In List Or Combobox

Jul 11, 2013

I am still trying to get a hang of development in access 2010.

I would like to design a form with a listbox or a combobox which holds all 8 of my reports (a table has all the reports), with a Print and a Preview view buttons. In addition, the user must be able to select if they want to view the report by month, quarter and the year in question.

How do i have a specific report print or previewed based on the value selected in the listbox or combobox and the date criteria.

View 4 Replies View Related

Forms :: Copy Specific Fields From Selected Record To Specific Fields In Subform?

Jul 9, 2015

I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.

Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.

View 12 Replies View Related

Lookup Fields

May 18, 2007

I have two tables(tblInvoiceItem and tblItems). On both of these tables there is; ItemNo and ItemCost. tblItems already has various records about items. I want to be able to have a lookup field for ItemNo on tblInvoiceItems which will populate the ItemCost from tblItems.

Basically when I pick an item number(lookup field on tblInoviceItem, looking up from tblItems), I want the price which corrisponds with it to appear in the correct field(ItemCost on tblInoviceItems).

Hopefully I explained that okay :confused:

View 5 Replies View Related

Duplicating Record With Selected Fields Only

Mar 28, 2015

Is it possible to duplicate a record with selected fields, to be copied into a new record. ie if there are 10 fields, i only need 8 of them (no need for timestamp field etc form existing record).

View 1 Replies View Related

How Bad Are Lookup-fields Really, For Performance?

May 28, 2007

Hello there,

I've lately come across some posts that condemn using lookup fields in tables. But how bad is this really? I mean it's quite a nifty feature, and it'd be really too bad if it hogs up resources, thus forcing us to leave it alone for performance considerations.

I'd appreciate if any of you guys/gals with more experience on the topic would like to share some, so I can take a better stance for future developement.


Regards,
Jaime

View 10 Replies View Related

Lookup Value Based On 2 Fields

Jan 16, 2008

I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.

I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".

Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.

So the TblLevels looks like this:

Score.....Technical.....Operational.......Admin
100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3

So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"

any ideas on how i can do this one?

View 10 Replies View Related

Query, With Lookup Fields

Feb 11, 2008

Hi,

I have a table that has 3 columns.

Issue, Review, and Information.
These 3 fields get their data from the same lookup table that holds some code values.

So I'm trying to create a query to export to excel that shows the data for this table, but for the lookup fields, I just get the ID's from the lookup table.


So the table is like this now

ID...Issue...Information....Review
1.......2..........5...............1
2.......1..........12..............6


So I would like to be able to display what the ID numbers actually are, but having trouble getting a query to do this for all 3 fields.

I can run a query that shows 1 field's but not all 3.

what I would like to see

ID.....Issue...Information....Review
1.......2a.........4c.............e5
2.......1a.........3c.............2F

all 3 columns use code_ID from the lookup table.

Is there a way to do this? or should I have used 3 different lookup tables for these?

View 13 Replies View Related

Lookup Fields In Forms......

Feb 12, 2005

I am working on a project that I know I am making more difficult then it need be...... Maybe because I started with a form and tried to build everything else around it. The database looked bad...... Now it looks good... normalized as I can see it... But trying to get the information in is being difficult.
The DB has customers, thats no problem. These customers have inspections done to areas for cleaning.
I have done the areas in a drop down, in one table, and the task to be rated as a dropdown in another table. Then finally a rating table....1 to 5.
It is very easy to go in in datasheet view and input the info for each area, then pick a task from the next dropdown and then pick a rating from the final.
Problem is this won't be as easy for someone else to input. So here is the form problem.
Heres how I would like to lay it out. An over all inspection form. Areas inspected and rating (average)
I will use a command button from the main form to open up say "area1"
On the area one form is the first problem....
I want 6 different task to give a rating to...... But these task are on a dropdown....so I can not bring over the field more then once.... or I dont know how..It's getting confusing and I don't think it should be.... its a pretty simple one.
So can anyone guild me to a databse similiar to this? I need to see how one like this is done.

Thanks

View 1 Replies View Related

How To Combine Two Fields In A Lookup

Apr 25, 2015

The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?

Note: I just did it in the Lookup query.

Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.

And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?

View 8 Replies View Related







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