Newbie Combo Box - Form/subform Dlookup ?

Jul 24, 2005

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?

Patrick

Victor, Montana

View Replies


ADVERTISEMENT

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

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 :: Filtering A Combo Box On A Subform From A Combo Box On A Main Form

Apr 27, 2013

I have a Suppliers database which contains a form that will allow me to place orders with Suppliers.The Main form has a combo box that allows me to select the supplier. The combo box is called SupplierID with the following:

Row source: SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName;

The subform is called Stock Subform witha combo box called ProductID with the following:

Row source: SELECT DISTINCT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Products.Discontinued FROM Suppliers INNER JOIN Products ON Suppliers.SupplierID=Products.SupplierID WHERE (((Products.Discontinued)=0)) ORDER BY Products.ProductName;
Event Procedure - AfterUpdate: Private Sub ProductID_AfterUpdate()
On Error GoTo Err_ProductID_AfterUpdate
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

[code]..

The Link fields are done on the Purchase Order ID (PONoID).What I want to achieve is to select the supplier from the combo box (SupplierID) on the main form and then the combo box (ProductID) on the subform to filter to only show products directly supplied by the Supplier selected on the Main Form.

View 11 Replies View Related

General :: Subform Combo Box Restricted By Form Combo Box

Mar 28, 2014

I'm an Access newbie and I'm having trouble restricting a combo box on a subform based on the selection of the combo box on the form.The combo box (Combo1) on the main form (FrmOrganisation) gives a list of companies, I want the combo box (Combo2) in the subform (SbFrmEmployees) to list people only from that company. I then have a subform within that subform that displays information about the employee.

View 3 Replies View Related

Newbie Question Regarding The Combo Box Wizard

Jan 11, 2006

When entering the setup data in the Wizard for a Combo Box and toward
the end it says:

Microsoft Access can store the selected value from your Combo Box in your datebase, or remember the value so you can use it later to perform a task.

Is one chooses the 'Remember the value for later use option' - where exactly
does MS Access store this information? I take its stored in some variable somewhere but where I have no idea.

Im sure the answer to this question must be incredibly simple but if someone could help me I'd be very grateful.

Thanks
Lee

View 2 Replies View Related

Dlookup And Combo Box

Oct 26, 2004

I am trying to get a dlookup in a textbox on a form to work and cant figure it out. It uses a selection from a combo box on the same form.

combo box name: service_type, source = tbl_Support_Service_Types
field one is a number, name = account_number, is the primary, is hidden, is the bound column
field two is the text description the user will choose, name = service_type
note: tbl_Support_Service_Types has a third column not used in the combo box called monthly_payment

In the control source of the text box I have tried several variations of the following dlookup:
=DLookUp("[monthly_payment]","[tbl_Support_Service_Types]","[service_type]= '"&forms!frm_Accounts!service_type"'")

I know the following part works:
=DLookUp("[monthly_payment]","[tbl_Support_Service_Types]","[service_type]=
It is crashing in the criteria part

I have also added the third field to the combo box and tried the following (which I prefer)
me!account_type.column(3)

I always get the same result, ?Name

View 5 Replies View Related

Modules & VBA :: Dlookup In A Subform?

Apr 22, 2015

I am trying to dlookup a field (field name [client]) which is on a form (form name bkpo)

The code i use is this

CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BKPO]![Client] & "'")

It works just perfect however if I use the form BKPO as a subform then the code doesn't work

The form is BK1b

and the subform is BKPO

I tryed this but doesn't work

CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BK1b].[BKPO]![Client] & "'")

I have tryed several other options like [Forms]![BK1b]!BKPO.[Client] or [Forms].[BK1b]![BKPO]![Client] but nothing

View 7 Replies View Related

Combo Box Synchronization Between Main Form And Subform?

Jul 17, 2005

I have a main form with a subform. On my main form, I have a combo box set to "find a record on my subform, based on what I select in the combo box." This works great! At the same time, I want to use this main form combo box to synchronize with a combo box in the subform to display a value based upon the value selected in the main form's combo box. I have tried several things and searched Access newsgroups without success. I am using MS Access 2002, producing an Access 2000 database. Has anyone got any ideas? I could really use some help. Thank-You.

Denny G. :confused:

View 5 Replies View Related

Open Subform Based On Form With Combo Box

Oct 27, 2005

I have a main form (FRMProspects) with a combo box (ComboCoName) that has a query as it's record source to sort the records displayed in it. It is bound by the CustID (autonumber), but displays the CompanyName.

What I want to do is open a sub form FRMNotes, based on the Company name that is selected in the ComboCoName. So for Company XY, I want to click on the Notes command button to open up all the notes for that company only (filtered). FRMNotes is based on a different table, but TBLProspects and TBLNotes are linked by CustID (one to many).

What code do I need for this? I am treading water with VBA!

View 4 Replies View Related

Forms :: Referring To Form And Subform Combo Box

Jul 21, 2014

I have a form named frmMain that contains five buttons that lead to subforms, one of which is named frmDeceased.

In frmDeceased, I needed to create a combo box that relied on selections from the combo box before it. I was able to do this successfully in frmDeceased, but then today when I opened the database and opened frmMain, I realized that the query fails when frmDeceased is opened via frmMain. It works perfectly if I just open frmDeceased directly.

The first combo box is ROHE and the second is Iwi (which is dependent on the Rohe selected).

The query for Iwi has this criteria: Forms!frmDeceased!cbo_ROHE

When I open frmDeceased through frmMain, I get the 'enter parameter value' message showing "Forms!frmDeceased!cbo_ROHE".

I tried changing this query to: Forms!frmMain!frmDeceased!cbo_ROHE, but it didn't work.

View 14 Replies View Related

Queries :: DLookup With Combo Value?

Feb 21, 2015

i have an access table.i m using Dlookup function for select data in dlookup , i want to sel field as combo box value, and i want when i change combobox value by select from list ,dlookup result will be change .

how can i do it.

View 3 Replies View Related

Dlookup To Replace A One Field Subform

Jan 3, 2007

Thought this would be a good solution, but having trouble with the syntax.

=DLookUp("[StartDate]","[tblJobHistory]","Me.[EmployeeID] =" & tblJobHistory!EmployeeID & " AND Min([tblJobHistory]![StartDate])")

I am getting the dreaded #Name? can I use the Min command like this?

Any help appreciated:)

View 6 Replies View Related

OnOpen Form Event Filter A Combo Box In Subform

Jan 18, 2006

On clicking a button to open a form I want to filter a list in a combo Box located on the forms subform

The main form is called 'Frm_orders'
The sub form is called 'sub'
The combo box field is called 'CostCodeID'
and I want to filter column 0 of the combobox which is Tbl_InvoiceCostCodes.Type to show records with a type of 'Repairs'

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

Forms :: DLookup Not Working In Subform - Keep Getting Error

Apr 23, 2014

I have a subform that has the Expense Code from the Expense Codes Table and I want to look up the Description (yes I know its not correctly spelled in the formula but it is in the table)

=DLookUp("Expense Discription","Expense Codes","Expense Code=" & [ExpCombo])

But I keep getting "error" returned

I have double checked spelling field names etc...

View 6 Replies View Related

Click To Trasfer Data In Field From A Combo Box In One Form To Another Subform

Nov 26, 2007

Hi,

I have a form build up with a subform in access 2003. Then I have an Insert button on this form which when clicked opens another form which is build up with a combo box. Now, when I select a field for example (Student_ID) from the combo box I would like to be in a position to click it once (the respective student_ID) so that the student_ID in that field is transferred to the subform of the initial form, which has a Student_ID field to store the respective student_ID in the table with the Subform.

How can I do that?

Any help will indeed be highly appreciated.

Regards

View 2 Replies View Related

Modules & VBA :: Using Form Combo Box To Control Subform Button Visible Or Not?

Nov 24, 2013

How to use the combo box from the form to control subform button or textbox visible or not?

View 5 Replies View Related

Combo Box Filter In Subform Showing Blank At Form Opening

Feb 26, 2015

In the Purchase Order details form a Supplier has to be chosen via a combo box. Based on that another combo box in the subform displays products only from this supplier (Products table is linked to supplier table).

I managed to let the subform combo show only relevant products using criteria referring to the main form combo box. Also other product data will show accordingly in text boxes. So far so good.

However when I close the form and reopen it the subform combo box is blank, other text boxes still show the right values. If I remove the filter criteria for the subform combo then all fields show all data correctly. (However the combo box is unfiltered again 8-/)

So somehow the 'criteria' prevents the combo to show the value that was previously chosen.

View 2 Replies View Related

DLookup Function Using Two Combo Boxes As Criteria

Oct 10, 2007

I have a form where I am using two combo boxes the second combo box fields are being based on what is selected from the first comb box. When I enter the DLookup criteria into the text box I am trying to lookup based on the selection of the combo boxes it returns nothing. Here is what my DLookup looks like:
DLookup("[BranchNumber]", "[tblAllADPCoCodes]", "[ADPCompany]= '" & [cboADPCompany] & "' And [LocationNumber]= '" & [cboLocationNo] & "'")

View 1 Replies View Related

Forms :: Requerying Values In A Subform After Updating Combo Box In Main Form

Apr 21, 2015

Within my main form I have a combo box called "workgroup." Also in the main form is a subform called "sfrmSubmissionRecords," and within this subform is a combo box called "covermemo."

Each cover memo is assigned to a specific workgroup, so my intention is for each time a new workgroup is selected from the dropdown, the covermemo combo box in the subform becomes populated only with the covermemos associated with that workgroup. I'm almost finished except for the final step when I try to make the values regenerate...Access says that can't find the referenced form "sfrmSubmissionRecords"

This is the code that I'm using:

Private Sub cmbWorkgroup_AfterUpdate()
Forms![sfrmSubmissionRecords]![cmbCovermemo].Requery
End Sub

View 2 Replies View Related

Modules & VBA :: DLookup Fill Textbox After Combo Selection

Apr 29, 2014

I'm a novice when it comes to Access 2010, and I'm having trouble with DLookup syntax, and am going nuts. I have tblLookup, tblFees and Costs, and frmFees and Costs. All data entry is done in frmFees and Costs.

The tblLookup lists plaintiffs and the matter related to them. In the form, I made a combo box [Combo13] for plaintiffs and a text box for Matter [Matter], but whenever I enter the plaintiff, I get an error.

The error is:
Run-time error '3075':
Syntax error (comma) in query expression '[PlaintiffName]=The Plaintiff I Typed in'

The code I'm trying to use:

Private Sub Combo13_AfterUpdate()
Me.Matter = DLookup ("[Matter]", "tblLookup", "[PlaintiffName] =" & Me.Combo13)
End Sub

[PlaintiffName] is the column name in the tblLookup.

I looked around the forums but couldn't find anything that matched my setup.

View 3 Replies View Related

Forms :: Show All Records In Datasheet Subform On Form With Combo Box Filters On Load

Apr 1, 2015

I am having an issue trying to show all records when I load my form. It loads correctly when I don't have a record source for the main form. However, the combobox filters will not work. My goal is to have users be able to use the datasheet, subdatasheet and combobox filters. I can get the form to work just fine when I link the Main form and subform, but when the form opens it is filtered on the first record. I have been successful with this approach when using other forms, but they didn't require a subdatasheet.

Is there anyway I can have the comboboxes to filter yet be able to show all records until the user selects filters? Is this possible?I finally got my Manager to agree to use a database instead of Excel files based on this form setup and I need to make as "user friendly" as possible (look and function like a spreadsheet) I attached some pics of the form along with the code for the filter.

View 1 Replies View Related

Forms :: Existing Data In Field Of Subform Also Filtered By Combo Box On Main Form

Mar 7, 2014

I have a form with subform (datasheet) .

On my subform i have one field which shows the

productId ( which is filtered from combo boxes on main form) .
productId contains two columns
1 bound column(id) ( hidden with width 0 )
2 column (desription) ( shown with width 2 )

Now after entering the data when i come back to the form again . It hides the data from the productId field on subform , because data in combo box (on main form) doesn't match. Although the row is showing up , only the text in field (productId) is hidden.

Now when i change data in combo box on main form to match the ProductId , then the productId field text shows again. after changing combo box it hides . What i need is the existing data should not hide when i change the combo box selection.

see pic 1 where product matches and pic 2 where not. please note i am using master child relationship on the form/subform.

View 14 Replies View Related

Forms :: DLookup With Multiple Criteria With Label Caption Or Combo Box Column

Aug 14, 2015

I want to result as dlookup function in access 2007, when I use dlookup like this

=DLookUp("GPFDed","[SalaryAll]","[EMPID]='" & ![Cmb_EMPID] & "' And [SalMonth]='" & "Jan" & "' And [Salyear]='" & [me]![Label17].[Caption] & "'")

it gives #Name?

What is the problem and how can i change the label caption to Combo box column 2 value...

View 1 Replies View Related

Forms :: Get Subform To Requery After Choosing A Name From Combo Box On Subform Itself

Jul 26, 2013

All I am trying to do is get a subform to requery after choosing a name from a combobox on the subform itself, thus updating the records on the subform. The main form name is "BasicTestOneF" the subform name is "ICminiBasicF" and the combobox name is "Combo4"

I have the combobox requery on After Update, but I still get the "Enter Parameter Value" popup. I've spent the last hour and a half just trying to get this simple thing to work and have gone through I don't know how many sites and forum posts, tried every combination of VBA code I've found, and I still can't get it to work.

View 14 Replies View Related







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