Forms :: DLookup - Changing Value In A Form

Feb 26, 2014

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
.................................................. ..................................................

View Replies


ADVERTISEMENT

Queries :: Changing A Join Breaks The DLOOKUP?

Nov 20, 2013

Bit of a strange question / problem. I am using a Dlookup between two queries. Everything runs fine is I leave the join as option 1: only include rows where the joined fields in both tables are equal. But if I change it to number 3 Include ALL records in right table in left it returns a syntax error on the query.

I can run the same query with same relationships of another table and it is fine!

View 3 Replies View Related

Forms :: DLookup To Return Form Load

Dec 12, 2014

I am trying to create a multi-user web-based database where all users log-in and see different forms based on a field in the user table.

I am trying to use a DLookup to return the name of the Form I want open based on the command box that shows the userID and UserName

DoCmd.OpenForm , acNormal, DLookup("field", "table", "[UserID]=" & Me.cboName.Value)
cboName row source
SELECT [table].[UserID], [table].[UserName] FROM table ORDER BY [UserID]

View 5 Replies View Related

Forms :: Refresh DLookup Data On A Form

Aug 17, 2014

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?

View 1 Replies View Related

Forms :: DLOOKUP Needs To Reference Form Or Subform

May 16, 2013

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

[code]....

View 1 Replies View Related

Changing Sub-forms On The Same Form?

Dec 29, 2005

Is it possible to change which subform is displayed by pressing a button, on the same form?

Each subform is based on a parameter query if that has relavence.

View 10 Replies View Related

Forms :: DLookup Values As Search Criteria On A Form?

May 13, 2014

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?

View 9 Replies View Related

Forms :: Refresh Contents Of DLookup Field On A Form

Aug 10, 2014

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?

View 5 Replies View Related

Forms :: DLookup - How To Get Combo Box To Display Data Into A Field In Form

May 16, 2014

My intentions are to get my combo box to display data into a field in my form, when it is changed/selection is made. So far my form has the following:

Combobox: CBOCharacters
Field: CharacterGender

Both of these are in the same table; tbl_Characters. The data type for CBOCharacters is a number and its called CharacterID in the table.

So far I have created this piece of code:

Private Sub CBOCharacters_AfterUpdate()
Me.CharacterGender = DLookup("CharacterGender", "qry_StillNeeded", "[CharacterID]= " & Me.CBOCharacters)
End Sub

When I run this event procedure i get an error:

Runtime error '2471'

The expression you entered as a query parameter produced this error '[CharacterID]'

View 3 Replies View Related

Forms :: Changing The Password In The Table From A Form

May 9, 2013

I have a login button in which if the username has an empty password or the user is his/her first time to login, another form will appear which is the Set New Password. How can I change the password of that current user, my Set New Password Form has two fields which is 'txtSetPassword1' and 'txtSetPassword2' and an OK button.

The user must enter a new password and a verification password, if the 2 fields matched, the new password entered will change the password in the table. The table name is 'tbl_UsernamesQry'.

I have done it but the main problem is, it changes the other's users password not the current user.

Here is my code for the OK button of that form Set New Password:

Private Sub btnSetOk_Click()
If Me.txtSetPassword1.Value = Me.txtSetPassword2.Value Then
Me.txtSetPassword1.SetFocus

[Code].....

View 11 Replies View Related

Forms :: Form Is Changing Table Data

Aug 29, 2013

I have a form that allows users to click on an item in a listbox and it brings them to the selected record in another form. However, everytime I close the form and open it, it changes the client name in the list to the client ID.

List is set up like this

Client ID | Client Name| Order Date
1 Mike 2013-08-04
2 Jon 2013-08-15
3 Mark 2013-08-17
ETC...

Turns into this on close - Changes the client name to the ID of the last item selected before close

Client ID | Client Name| Order Date
1 Mike 2013-08-04
2 3 2013-08-15
3 Mark 2013-08-17
ETC...

Select statement is:
SELECT Client.[Client ID], Client.Client, Client.orderDate FROM Client ORDER BY Client.orderDate;

Code is:
Option Compare Database
Private Sub Command22_Click()
'opens the form with my subform that holds the table data
On Error GoTo Err_Command22_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Client1"

[code]...

View 5 Replies View Related

Forms :: Form Is Changing Data On Load And Close?

Feb 8, 2014

I have designed a form using the wizard from a table which records transactions for an entity.

I have a Primary Key (auto number), entity id, transaction amount, description and date fields in the table.

I look up the entity using a list box, then populate unbound fields then use CurrentDb.Execute "INSERT INTO in order to save the new record.

When I open the form for use, it automatically changes the entity id of a previously added record to the most recently selected entry in the list box. I am unsure why it does this. It seems to be editing previous records when you change the value of the list box.

View 8 Replies View Related

Forms :: Changing Combo Box On Form To Allow Entries As Well As The List?

Jun 3, 2014

How do I change a combo box that I have on a form so that it will allow entries as well as edits?

View 1 Replies View Related

Forms :: Changing Form Picture Based On Combobox Selection?

Jul 30, 2013

I would like to set up a picture in the form that changes based on a combobox selection, for example if you select from combo box list "design1", a picture that have a name : design1 will appear as a background to the form ..

View 8 Replies View Related

Forms :: Changing RecordSource Of A Form Based On Field Values?

Feb 16, 2014

I have a simple add form that will add an entry to one of two tables: Box 1-1, or Box 1-2.

They have the exact same fields. In the add form, I want the user to specify a Rack field and a Box field. Box 1-1 would be Rack 1, Box 1. Box 1-2 would be Rack 1, Box 2 (there will be more tables later, but just using two for now until I get it working).

But right now my form always adds to the table Box 1-1 - I can't get it to switch.

I've added the code I have so far below. The first part is what I'm having trouble with, the second part just uses a Submit button to add a new record - I'm just including it in case it's interfering in some way I'm not seeing. I'm running Access 2007.

Code:
Private Sub Switch_BeforeUpdate()
If Me.Rack.Value = "1" And Me.Box.Value = "1" Then
Form_Add.RecordSource = "Box 1-1"
ElseIf Me.Rack.Value = "1" And Me.Box.Value = "2" Then
Form_Add.RecordSource = "Box 1-2"

[Code] ...

View 7 Replies View Related

Forms :: Sorting Recordset Clone Without Changing Order By Of Form?

Sep 27, 2013

I would like to get the Min + Max values of the data currently in the form, but without changing the sorting currently on the form.

So I was hoping for this, but it is not working. The data in the recordset are not sorted.

Code:
Set R = Me.RecordsetClone
R.Sort = "SendOn ASC"
R.MoveFirst
MinDate = R!SendOn
R.MoveLast
MaxDate = R!SendOn

Any other method except iterating through the entire recordset?

View 3 Replies View Related

Forms :: Can't Get DLookup To Work

Oct 16, 2014

I'm trying to build a form that Pulls data from several tables, though will update only one. I'm having issues right now with a field that is supposed to draw from a query. I made a query that is supposed to concatenate different fields in another table, look for duplicated words and remove them, but although that isn't working yet, that's not my issue. My issue is that the DLookup in the form isn't even trying to look at the query. The query prints out workshop IDS and a concatenated field right now, so there is data to pull.

=DLookUp("[Expr1]","[Posting Title Query]", "[WorkShop_ID] =" & [WorkShop_ID])

Query name: Posting Title Query
Query Reference Field: WorkShop_ID
Query Data Field: Expr1

Form Name: Sessions
Form Reference Field: WorkShop_ID
Form Table: Session List

View 7 Replies View Related

Forms :: DLookup Cannot Update Txt Box

Jul 23, 2014

I have a new Customer Form(Profile) and I have the same customer data in another Table(Contacts).

I create a Dlookup... the system pull the data correctly from my Combobox but when I try to close the form, the system display a error like: The first name is empty...

I think the Dlookup need something to update the new table.

This's my actual Dlookup...

=DLookUp("[Last Name]","Contacts","[Combo378]='" & [Forms]![PROFILE]![Combo378] & "'")

View 1 Replies View Related

Forms Problem -PLEASE HELP -DLookup Function

Jun 26, 2007

Hi

I am having a problem with computation of monthly actual generated from the Amount column which is in a purchase order table..... The monthly budget amount is generated in a query named category query... I have computed like Jan Feb Mar till Dec the column fields in the query.... A form named PO form has been created with the category item combo box ....

The problem is when a user selects a combox box he or she is presented with the monthly actual textbox that shows the amount computed( on a fiscal basis(not calendar days)) and that is done by looking up at the date converting into a month and then computing the amount for that particular category item selected...sum done if there was the same category item like repairs and maintenance done for this amount on the same month different date...I need a way to look up at the date convert into a month and then show the sum in the field textbox using the Dlookup function ..>Can this be done??)

Monthly budget I cant figure a way to get the monthly budget by looking up at the date and show the computed amount on a "fiscal basis" since the monthly budget for different months jan feb mar is computed seperately in seperate fields in the query

Please help me!!! this is URGENt!!!

View 3 Replies View Related

Forms :: DLookup - Not Valid Prompt

Jun 10, 2013

Is it possible in Access 2010 to have a prompt "Not Valid" in a dlookup?

If =DLookUp("[Lot]","tblBarcode","[Inuse] = -1") is No, then the message appears.

View 1 Replies View Related

Forms :: Setting Default Value Using DLOOKUP?

Jun 17, 2013

I have a form for new customers. One of the fields is the product they have chosen ("new model", "old model", etc.) in a drop down Combo box. I want default pricing to appear in the form based on the what user selects for the product. But, I am getting nothing shown in the price box after choosing the product.

I've tried two approaches but with no luck. What am I doing wrong? This is in the default property for "price"

=DLookUp("[priceDefault]","tbl_products","[product]= '" & [productChosen] & "'")

=DLookUp("[priceDefault]","tbl_products","[product]= " & [Forms]![frm_CustomerRegistration]![productChosen])

I'm using Access 2007 and Windows 7.

View 9 Replies View Related

Forms :: DLookup As Source For Text Box

Jun 2, 2014

Database has a form on which the user is to select a PP Type. This is a choice of up to 8 different kinds of types. There is a table in the database that keeps track of what each of these types are, either a pallet or a case. I need to create an unbound text box on the form that will display wether the chosen Type is a pallet or case. I understand DLookUp is the way to go on this but I am having a problem with the where clause part of the code.

What I am trying to write here is display in the text box the "Pallet Case" field in the "PP Type TBL", where the "PP Type" field of the "PP Type TBL" is the same as the selection in the combo box name "Type". PP FRM is the name of the form I am working on and where the text box is as well as the combo box.

DLookup("[Pallet Case]", "[PP Type TBL]", "[PP Type] = " & Forms![PP FRM]![Type])

I get #Error in the text box. My confusion is how do I refer to the PP Type as it is int he PP Type TBL in the criteria?

View 6 Replies View Related

Forms :: DLookup To (print) Another Table's Value

Mar 29, 2013

I have two tables each containing fields Brand, Form, Area. Table 1 has some other information that needs to be gathered (data entry) and Table 2 is just a reference table for changes to these areas. This reference table has an additional field labeled area point value which is the value I want to "print".

The form is based off of Table 1 and has all of the fields I want the users to input. Stripped down, I have three combo boxes for the user to choose Brand, Form, and Area...I also have an unbound textbox control where I want the area point value to based off of the value of the three aforementioned boxes.. I believe this can be achieved with a lookup but I've never actually used a lookup in a control this complex before...

View 1 Replies View Related

Forms :: Using Parameters With Dlookup Function

Mar 26, 2014

I am trying to use parameters for my dlookup =DLookUp("[jan]","[ctbDailySegments1]","[PCC]='0hq'")

How can I replace [jan] with a prameter to pick from a text field formated as date. and 0hq from a combobox cmbPCC.

The function works perfectly as I change the jan and 0hq.

View 2 Replies View Related

Forms :: Multiple DLookup Syntax

Feb 24, 2015

Me.txtversion = "Ver:" & " " & DLookup("[txtVersionMajor] & " - " & [txtVersionMinor] & " - " & [txtVersionRevision]", "tblVersionInfo")

What's wrong with my syntax ? It's the "-" part that is the problem.

View 3 Replies View Related

Forms :: DLookup A Combo Box In A Table?

Apr 20, 2013

Iv got a Form (Form1).That has a combo Box (Description)and a Text box (Category).that refers to a Table (tblDescriptionLU)With a text Field (Description) and a Lookup Field (Category) to a Table (tblListOfCategorys)

The Code iv Put in is:

Private Sub Description_AfterUpdate()
Category = DLookup("Category", "tblDescriptionLU", "Description=" & Description)
End Sub

It returns a Error:

Run-time error '3464':
Data type mismatch in criteria expression.

View 6 Replies View Related







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