Form field to display query lookup value

Struggling here.

Have a table (Customers) which has lookup fields for codes. This is expanded to a query (customers extended). The query displays the lookup value just fine. I have a form which is split and is used to search for customers based on a number of things including the codes. Unless I use a combo box I cant get the results to display anything other than the ID or hidden value of the lookup value. Using a combo box puts multiple drop down arrows on the form. I truely would like to not have this. Can you display lookup values in a text box of a form?

I have tried =DLookUp("Code","CodesSIC","[ID]) and multiple variations. I believe the situation is, without binding the text box to SIC1, SIC2, or SIC3 it doesnt know what field to equate it to.

Any suggestions for displaying the lookup value in forms text box?


Post your answer or comment

comments powered by Disqus
I have a subreport that displays data from a query and creates a numbered list. I would like to display the result of the record's subreport on a form field. So far I have only been able to get the form field to return the last item on the subreport list.

I would also be open to comments on how to design my form query to place a numbered list in the form field. I have had trouble getting this to work as the query returns the same record again for each item on the list.

Hi, I was wondering if anyone could help me.

I have a simple database, consisting of two tables and one form. I'd like the form to display information mainly from the first table (table 1). For this, I have used the Form Wizard, which works well and displays as it should. My problem comes when trying to display data from table 2, without using the Form Wizard.

I have a simple field called 'Number' in 'Table 2'. I'd like this value displayed in a text box along with table 1's data. I've tried using the expression builder, which gives me something along the lines of:

[tbl_Table 2]![Number]

But this is displayed as:

#NAME

...when I open the form. Could anyone explain how I do this correctly please? All I'm trying to do is display data from other tables in the form.

Any help is much appreciated.

The Issue
I have a form that's working for 99% of what I need it to do; but I'm having one problem. The form is set up to track time spent; so naturally it has a start time and a time finished. The start time is set with a default value of Time() (access 2007). This part is working great.

Originally, I had the finished time set the same way; but was constantly having to change the value (which kind of defeats the purpose of having it show up on its own). What I am looking to do is have the time finished field automatically display when I click in that box as though I were going to type the field.

Solutions I've Tried
I've tried going into the properties of the field inside the form and adding the expression "=Time()" to the "On Click" field but nothing happens when I click in the field after doing this. I've tried doing the same thing in a few other property fields and it still isn't working. I've also looked at the property sheet for the table on which the form is based, but I don't see anywhere in the table to do this.

I have removed the default value from the time finished field so I know that is not what's causing the problem.

All help is appreciated!

I am assisting with an Access Db (2007) that was imported from 2000, and realize I am a beginner the more I learn about Access.

In the Db, there are many tables and forms, but one issue that has arisen - which we haven't been able to resolve - is within some forms. 'I will try to explain as best I can.

They are using a combo box that lists values based on the primary key that joins several other tables together. The problem is, the actual items they need (Column 2) are not in alpha order. For example, Column 1 on the table referenced by Combo box 3 is the primary key - a 2 letter code. The 2nd column is the actual item, the term we want the form field to display in alpha order (as the primary key abbreviations are a formal "code" that may not correspond with the actual item). We tried adding another field, simply 1-87, and then tried to have the form sort by that to no avail. Additionally, we only want Column 1 and 2 to display.

If attaching screenshots would help, I'd be happy to do so. Thank you in advance.

Jim

Simple set up,

I have a Sub form titled Training that has the the following two fields:

[Department],[Jobs]

The jobs is a pull down select box that contains all of the job titles for the entire plant. What i would like to happen is for the user to be forced to select the appropriate department first and then use the [Department] selected to feed the criteria to the query that the [Jobs] field is located in.
In addition i have been un-able to get the fields to be locked until the [Department] field is entered.
I have tried the following unsuccessfully:

Jobs Query Criteria:
=[Foms]![Training]![Department]

Hi, I have a question about queries and reports. I've only been using Access for a day, so I'll try to make it as easy to follow as possible.

What I have:
-Table A is a list of solar panels, each with its own unique ID. The table has the installation date for each rack, labeled INSTALLDATE written as MM/DD/YYYY.
-Table B has the same list of solar panels with their unique ID's. The table has the district each panel is in, labeled COUNCILDISTRICT assigned a number 1 through 12.

Now what I want to to is make two reports that show two different things:
-Report A needs to show the number of solar panels that were installed in each district in 2012 only.
-Report B needs to show the number of solar panels that were installed in each district in 2012 each month.

I've created two queries that give me the information I need:
-Query A lists each council district in one column (labeled COUNCILDISTRICT) and the number of solar panels installed in 2012 in the next column (labeled COUNTOFINSTALLDATE1). I got this query by going in the design view making the following columns:
--Field: CouncilDistrict. Table: tblB. Total: Group By.
--Field: CountofInstallDate. Table: tblA. Total: Count.
--Field: Year([InstallDate]). Total: Group by. Criteria: 2012.
-Query B lists council districts, months (1-12), and the number of solar panels installed in that specific month in that district in 2012. I got this query by doing:
--Field: CouncilDistrict. Table: tblB. Total: Group by.
--Field: CountofInstallDate. Table: tblA. Total: Count.
--Field: Expr1: Month([InstallDate]). Total: Group by. Criteria: Between 1 and 12.
--Field: Year([InstallDate]). Total: Group by. Criteria: 2012. (Unchecked Show).

Now, for some reason I'm not able to get the reports to display the counts that the queries display. For Report A, which shows installations in 2012 in each district, I have the control source for the text box set to CountOfInstallDate after selecting it through the menu. Yet every time I click on the report, it asks me to enter a parameter value for CountOfInstallDate instead of giving the values from the query. The same thing happens in Report B, I can't get it to display the CountofInstallDate values for each month of 2012 in each district.


Any help on what I'm doing wrong? Thanks!

I have a query that returns down hrs for different modules. I would like to add a field to this query and have the value be the down hrs if the value = a certain value. Very hard to explain please see the table below.

System - Module - HrsDown
1234 - Ch 1 - 1
1234 - Ch 2 - 1.5
1234 - Ch 3 - 0
1234 - Ch 4 - 3

So now I want to add a field Tothrs and I want it to add only if the module = Ch 3 and Ch 4

System - Module - HrsDown - Tothrs
1234 - Ch 1 - 1 3
1234 - Ch 2 - 1.5
1234 - Ch 3 - 0
1234 - Ch 4 - 3

Newbie here. I have a table that has 40 questions. I want to create a transaction table for the 40 question. So I have another table that has the entries in them This table has the answers to the questions as yes and no, the question number and the description. So there are 40 entries in the second table that match the 40 question table. What I want to do is dynamiclly set the description field's default value bases on the second tables question number to match the 40 question table. I am looking for where I would enter the select statement in the forms field to get the default value from the 40 question table. I am using Access 2010. Has any one does this? If so where and how.

Hello Dear Friends, I have a question about Synchronizing Sub-Form Field To a Text-Box!

I have a Main form on which i use Sub-Form and text-boxes (Sub-form is Synchronized with main table data)
I want to display Snumber and Barcode from Sub-form in Unbound text-boxes, I used the function: =DLookUp("Barcode","CODE") the field is displayed but it is not synchronized I think there must be add something like : "[DATAID]=" & [ID], but can't implement it correctly.

Thank you In advanced

I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control.

Any help would be appreciated.

Thanks

darrrellx

Is there anyway I can add a sort of count field to my query, for example what I require is the result of my query to include a column which counts the rows in the queries.

For example:

PositionID NumOfVotes Count
5 5 1
6 2 2
etc.

Any advice please?

This should be any easy one to answer...
I am ranking sales data descending by dollars sold but I would like to add a field to the query that will produce a ranking from 1 to XXX, any ideas?

I would like to copy the field from a form field to a sub-form field.

I don’t really care if this is via a macro button in the sub-form or as an event associated with a record entry to the sub form – which ever is easier (or possible!)



I have a series of tables:

"TBL_Names" (represented by the first 5 fields on the left)
"TBL_Methodologies" (first sub-form on left side of image: fields “Names” & “Methods”)
"TBL_Systems" (second sub-form on left side of image: fields “Names” & “System”)
Etc. etc.

The common field for all tables is “Name”.

What I want to do is enter new records into "TBL_Names" via the main form and all other tables via the sub-forms where required, however I do not want to keep typing or physically copying the name “Joe Bloggs” (as in the example shown where step 1: I enter "ISO 9000" in the methods field then step 2 occurs to copy the name from the main form field to the sub-form field). Not all subforms will need this action every time, so I imagine I will have to make it sub-form specific and apply to each sub-form separately...

Am I right in assuming I can enter a new record for “Methods” in the appropriate sub-form and then have an event copy the contents of the “Name” field from the main form to the “Name” field in the sub-form – either on enter or click (or any other appropriate action)?

(Please ignore the presence of the copy button in the sector 1 sub-form – it was a failed attempt to achieve this process via macro !)

Windows XP SP2
MS Access 2003

Hi - brand new here and would appreciate any help.

I am using Access 2012. I have a table (Character) where I have numerous look up fields. A few from other tables but most from Queries. In both cases I am storing the unique ID . When I created a form (automatically) it displays query lookup differently from those based on tables. For the lookups based on a table it always show the lookup not the ID (this is what I want).

Character_Type for example shows "NPC".

But the lookups based on a query it displays the ID not the lookup (unless you click the arrow).

Name_Prefix for example shows "7" not "King"

Is this normal? I want the query lookups to display like the Table looks ups do.

Thank you, in advance, for you advice.

Tried to display an image here or a link but evidently I do not have enough posts hope the words are clear enough.

I have a very simple Access form that will be used to insert records into a SQL database.

First field is 'pvLot' and it is a Combo Box whose values are driven by a separate pvLots SQL table.

Second field 'waferID_slot' is a varchar(3) (in the SQL database) and common values would be '001', '002', '003', etc...

For the third field 'pvCellSerial', I need the form to INSERT to the SQL database a concatenation in a sense. The value in this field should be [pvLot]&"-"&[waferID_slot]. Using the 'Control source' property I can get that string to display on the form, but it appears to be as read-only property as the SQL insert fails as the third column is NULL upon insert.

Novice at SQL development and have used Access for several years but primarily for complex ad-hoc queries, so little experience with forms.

I've tried everything I can possibly think of to get this to work. I've also looked at other threads and I still couldn't get this to work. The code for the list box in my form is Forms!Display_Engineering_Jobs_Report_Inputs!Repor t_Column1. I am trying to set the value in this list box as the input for the field on my query. This value also corresponds to a heading within a table I have. The information under the heading in this table is what I am trying to show in the query. Please help! Also, I'm pretty new to Access, so explain it as if you were writing a page in "Access for Dummies." Thanks!

Let's suppose for a minute that I have several different fields in a table, and they are ABC1, DEF1, GHI1, JKL1, etc..

Now let's suppose that based on a form entry, I would like for a query to display the particular field I'm looking for.. For example if I entered ABC in the form, I'd want ABC1 to display in the query, and if I entered DEF in the form, I'd want DEF1 to display in the query.

My mind is a little frazzled at the moment, but is there a fairly simple way to do this with a calculated field or VBA, or anything?

Hi

I have a Form called Products. Each product is uniquely identified with a primary key called [ProductID].

Products contains a calculated field called [UnitsOnHand]. [UnitsOnHand] gets its values from a subform called ProductTransaction that exists within the Products Form.

ProductTransaction contains a field callled [Received] . At the moment [UnitsOnHand] will add up all the values it finds on the [Received] field to derive the [UnitsOnHand] value for that product at any particular time ie =Sum(nz([Received])) on [UnitsOnHand] .

I also have a Query AutoSell that outputs a value for each [ProductID].
How can I use these values for each [ProductID] generated by the Query to substract from the values on the [UnitsOnHand] calculated field on the Product form.

ViRi

I have an input form, "frmInvoice" that updates a "tblInvoice" table. While the form is still open and after data has been entered into the form, I would like to reference a field "UnitPrice" on the form from within a query to perform other calculations that will then be written to "tblInvoice." I will do this, if possible, via a command button. Can this be done? If so, can someone give me the syntax for criteria in the query? I understand how to write the data back to the table via a query but don't know how to reference the "UnitPrice" field in the query. I've tried forms!frmInvoice!UnitPrice but this didn't work.

Maybe simplier stated, I want the query to retrieve the Invoice number and Unitprice from the from so that it can perform a calculation that will be written to the tblInvoice table.

Any help is greatly appreciated.

In My Db I have a search form which contains a ListBox which filters a list of companies in the Db according to the relationship type (prospect, suspect, customer, partner or dead)

The listbox is fed by a query (QrySearchGeneral) In the Qry there is field 'relationshiptype' and in the criteria is

forms.frmSearchGeneral.CbRelationship This is where a user selects from a drop down list to select the type of contact for the listbox to display.

The combo is fed from a table. TblSearchGeneralRelationshipSource in that table there are two fields DisplayValue and QryCriteria
DisplayValue | QryCriteria
Customers | customer
Prospects | prospect
Suspects | suspect
All Live Records | "Customer" Or "Prospect" Or "Suspect"

Everything works fine expect the 'all Live records' option. When selecting it the listbox shows no records. There seems to be a problem handling 'Or' values and have so far been unable to diagnose why. Any advice appriciated.

Hi,

I searched the forum and found lots os thereads discussing how-to display query results in forms, sub-forms, etc. But I still can't figure it out.

I have a form which is bound to a table. I want it this way because at form-open I don't have the query. The query is actually built using the form.
The query works ok because I tested it as standalone.

I guess I am missing some code tying the recordset to the form or something like this.

Trying to display the results in a split form, I noticed I was getting all records in the table, not just the ones that matched the query.

I changed something and now I am also getting a recordset not updateable error!

Can someone please check my code below and point me all my mistakes?

Thanks,

Gerry


	Code:
	Private Sub Audit_Click()
Dim wrkspc As Workspace
Dim WorkBase As Database
Dim WorkRS As Recordset
Dim SQLstr As String
 
 
Set wrkspc = CreateWorkspace("", "admin", "")
Set WorkBase = wrkspc.OpenDatabase("Test.accdb")
 
SQLstr = "SELECT name, id FROM employees WHERE location=”London”
 
 
Set WorkRS = WorkBase.OpenRecordset(SQLstr)
 
Do Until WorkRS.EOF
  'the following are text boxes to which I try to assign the values
  TextName.Value = WorkRS.Fields(0)
  TextID.Value = WorkRS.Fields(1)
  WorkRS.MoveNext
Loop
 
WorkRS.Close
WorkBase.Close
 
 
End Sub



Guys,

This is a simple one, I'm sure.

I have a form that needs to display the Maximum Value of a Field in a related table, +1.

I have tried DMAX in the default value of the field in question, but I'm either putting it in the wrong place, using the wrong syntax or using the wrong function.

Any help would be appreciated.

Cybrooks

Thank for your help

I have Table1 and Table2 - one to many relationship
Table1 has a field Days_Till_Next_Calibration plus other fields
Table2 has a field Event_Date_Complete plus other fields
I created a query and added a calculated field in my query to get Due_Date
my query works fine.
I would like to display a form that will display on the top portion of my screen Table1 and all the entries in Table2 in my subform that would include the calculated field Due_Date

I found an earlier thread by Wazz that told how do display a lookup value
=DLookup("[surname]", "tblB", "[staffID] =" & Forms![frmA]!staffID)

but it did account for blanks or nulls or if a field (numeric) is zero/no value.. can you add to this? anyone?


Not finding an answer? Try a Google search.