I am having an issue with trying to display text data "Supplier-ref-1" from a Table called "Supplier Prices" which also has the "Item-code" field. I want to display it in a text box in my "Items" form, where the Item-code is equal to that in the Supplier-prices
I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.
=DLookUp("[Salary]","[Salary Query]")
How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.
I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".
I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code: If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things
I have a 2 unbound text boxes.1 populates the id number from a table.I want the other to display the first name where this id occurs. I am currently putting this in the control source of the seconded textbox
I have a form with multiple combo boxes. Once the combo boxes are populated, I need the text box to look up the value in the query based on two combo box selections.I am attempting to do a DLOOKUP, but I am obviously not doing it correctly because the result is #NAME?.
Here is what I have done: In Default Value section of the txtONE property sheet: =DLookUp([fieldTHREE],[qryMAIN],[cmbONE].[AfterUpdate] And [cmbTWO].[AfterUpdate])
In the "qryEmployeeWTax" i need to Lookup the [InitialTax] from the "qryreftbl_WTAX" based from the [ETPES] and [SalaryAfterStatutoryDeductions], so i am using this function:
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
here I have capture what is error actually when i make a DLOOKUP wizard.the box of wizard. there i want to make a relationship with table2. but we could see that table1 still show. yes right, it's reality making error and show message ""you can't modify the structure of table, because it is already in use by another person or process"and warning message ""Microsoft access couldn't launch the lookup wizard, or this wizard has been disabled...blabla...blaa"
on my laptop, ms access 2010 nothing happen the mattter and when i make DLOOKUP, parent table have been not seen.and make DLOOKUP Wizard is successfull.
why in my PC, when i make DLOOKUP wizard, parent table still show?what is this bug for MS access 2010?
I have a report with quite a few subreports in it. There are a number of calculated fields on the form, most of which use Dlookup to retrieve at least one of the figures required for the calculation. The Dlookup runs fine and the report opens but the calculated fields are devoid of data in Report view. When I switch to Print Preview view the fields are now populated. Below are two variations of the DLookup syntax I have used to try and alleviate this issue.
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[sub_AllHours].[Report].[8]
=DLookUp("[8]","qry_MonthlyTotalsByYearFirstAid","[ActivityType] = 'First Aid Injury (FAC) Reported in Safeguard'")/[Reports]![rpt_AllFigures]![sub_AllHours]![8]
Note that the field [8] specified here is simply a month number and forms a column in the crosstab query for the corresponding query name.
I would add the query referred to in the DLookup to the source query for the report but the source report's data is derived from a Crosstab query, which only accepts one data field (Access terms this as the value field.
The function returns a null except for certain values of SecCode. The value of SecCode ranges from 0 to 99999999. Some values of the code work but most do not. When I do get text returned, it does not match the document section. Is there a counter that needs to be reset or re-indexed?
I have searched for days and not been able to find an answer to a supposedly easy question.
I have a continuous form "Entry_Form" based off a table called "Today". I want to add additional criteria to the table using the form and have created a combo box that is based on a separate table called "Reason_Codes". "Reason Codes" has two fields "ID" and "Description".
When selecting the combo box "ID_Selector" I populate one of the fields on the form which then populates the corresponding field on the table. This works.
My problem is that I want to have a text box on the form called "Description_Code" populated automatically the "Description" based the ID chosen.
I have created the following DLookUp equation and placed it into the Control Source, but nothing is populated in the "Description_Code" field.
I know to most this is simple, but as a newer user putting everything in its right place is hard. Have checked multiple posts for my situation and cannot locate. I know I need to use DLookup.
Membership table used to built form has among others, a contract #1 field (9-digit Soc.Sec. #) and a Contract #2 field with another randomly assigned 9 digit number. Current form set up to accept contract#1 in an unbound text box which then autopopulates other fields pertnent to this member. Member should be giving us Contract #2 but that will not always happen. Want a DLookup so that if the #1 num is put in it will automatically input the correct #2 number.
Not sure where to put it and exactly how to format. Can someone please help me. Both fields on the same table.
I'm using Access 2010. I'm having trouble with dlookup.
Table name: tblTier2 Fields: Tier2ID, autonumber Tier2FieldType, text
I have an unbound list box on a main menu form.
The listbox control is numeric and called: lstItem
I tried putting the following in the textbox:
Neither of these worked: = DLookUp("[Tier2FieldType]","[tblTier2]","[Tier2ID]=" & [lstitem]) =DLookUp("[Tier2FieldType]","[tblTier2]","[Tier2ID]=" & [lstitem].[column](0))
I wanted to see if the lstItem was the problem, so I put ths following in the text box. : =[lstitem] (it returned the ID number selected from the list box, e.g., 809)
The DLookup works fine, just I want the value to be stored in the table. Please help me ! I have been browsing the internet for the whole day and i can't seem to find the right solution! Thanks!
At the moment I have a form (InputFrm) for operators to enter production processes into the system. Each one of these has a certain weight which the operators enter and it all stores in InputTbl, which works great.
However I am trying to have a running total of the kilograms inputted on each day displaying when a new record is opened. To do this I have created a query (DailyDataQry) which provides me a running sum of how much input has gone through the process, and I can clearly see the data in this query.
I am attempting to have a text box in the upper right of my form which displays this number as after a certain weight of input the operators are needed to perform checks.
This is the code I have at the moment in the control source of a text box (DailyInputTotalTxt), but it is just returning a blank:
The work date is generated automatically when the new record is created by having Date() in the default value of the work date (WorkDateAtxt) on the form.
Is there any way which I can use this value to lookup the current total of input contained within DailyDataQry?
I'm making a form for colleagues to use which will eventually populate a table. I'm using DLookup to populate some of the fields in the form from another table. The problem is that for a few of the fields in that other table, the values are incorrect.
(Obviously the most sensible thing to do would be to amend the source data but for various reasons I cannot do that.)
For example, "date built" in the original table might be "f" but that needs to be changed to "h", so I tried the following but it doesn't work: .................................................. ............................................
If DLookup("date_built", "dbo_meas_questionnaire", "[meas_id] = Forms!FrmRetestSearch.numOrigId") = "f" Then Me.TxtDateBuilt = "h" Else Me.TxtDateBuilt = DLookup("date_built", "dbo_meas_questionnaire", "[meas_id] = Forms!FrmRetestSearch.numOrigId") End If .................................................. ..................................................
Here goes my problem, and how I am attempting to solve it, but I am open to advice.
I have a table that contains all of the bird species in the US; (fields: common name, scientific name, species ID) I want to be able to update another table, (birds to chase) with particular bird species that I have not yet seen, (fields: species ID, common name, scientific name) and link that to a sub form that links to another table (chase birds locations) which shows geographic location data as to where I might be able to see new/unseen birds; (fields: species ID, date, other geographic data).
My problem is this. I created a form for the unseen birds table that allows me to only select a bird using a combo box of birds from the larger table of US birds. I want to select a bird using the common name in this box, and have it automatically populate the scientific name and the species ID into the birds to chase table. I link this tables to the location table using species ID.
A workaround that I have used is to close the form, run two update queries that populate the proper fields for scientific name and species ID and to re-open the forms. Although this works, when I return to the form it does not return me to the proper record, so that I can enter data into the sub-form.
Obviously this is a workaround and will work for me in the short run, but I want to learn how to do it properly within Access 2003.
I'm not a programmer and hence do not have access to the .NET tools or Visual Basic. Any hints for me out there?
How do you refresh all of the data on a form when the tables that are the source for dlookup fields on the form are changed by command buttons on the form?
I have a DLOOKUP which is used to auto populate a field on my Income Commitment Form based on a field on my Expenditure Commitment Form.It works perfectly when you enter a new commitment.Currently you enter a new Expenditure Commitment, save it but don't close it, then from the Expenditure Commitment Form there is is a button to enter a related Income Commitment. You save the Income commitment then close it, then close the expenditure form and you are on the Client screen with both these forms now showing as subforms, both have record selectors.
My issue is, that sometimes the Expenditure Subjective can change, so from the client form you use the record selector to open the Expenditure Commitment and select the correct Subjective, you then save it and close it. You should then click on the Income Record selector which opens the income commitment, but when you re-click on the Combo42 button, it doesn't reselect the Income subjective and I think it is because the Expenditure Commitment which is what it is looking up isn't open. My issue is how do I get the DLOOKUP to work and refer to the Commitment Form if it is a new commitment, or the Subform if I have gone back in through the record selector.Here is the code I have.
Private Sub Combo42_AfterUpdate() If (Me.Combo42 = 1) Then Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'") Me.OtherIncomeConfirmed.Enabled = False Me.OtherConfirmedBy.Enabled = False
I am trying to use DLOOKUp and Trim in a calculated control on a form - D look up works but when i add Left trim to trim a telephone number to just the prefix - it wont work
=DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form]![Data] & "'") - Works OK =DLookUp("[CSP]","[Csp_Codes]","[Data] = '" & [Forms]![Application_Frm]![CommsData_tbl subform].[Form]![Left](Trim([Data]),7) & "'") - appears to put square brackets around Left ??
So I'm working on something for housing. Each house has a "Property Reference". This property reference links to all other information on the property.
There's two tables, Referral (For a tenant, with the Property Reference as a foreign key) and PropertyInfo (Holding all the property information)
Basically I want to save users as much input time as possible, so I'd prefer if they could just enter the property reference for a person, and that populates the rest of the table.
I'm currently using DLookup on the main form where it displays the information related to the property reference, obviously it's not actually being saved into any fields.
Will this method be okay if I would want to search the records by the address on the main form?
I have a field on a form that is the results of a dlookup to a table.field. I want the value to be updated when I execute a button that is running a macro. How do I do this automatically, and not have to hit the refresh button?