Combo Box Problem With 600.000++ Records

Sep 21, 2005

Hi,

In my database I've got a table containing 648.000 movie titles (movie_id, title). this table is the datasource for a combo box in a form which connects the movie to a person (or an object, i.e. file, article etc.).

when typing the first few characters in the combo box (to add a new movie to a person), the combo box narrows down the selection accordingly.
but if the title requires more characters to by typed to narrow down the selection to the required one (i.e. "a,m,e,r,i,c,a,n, ,p.e.r" for "american perfect) and I'm typing to fast, it happens that (after pressing TAB to confirm the selection) access (2000) presents the error message "the microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time". after confirming the message, it keeps popping up several times until I change focus to a different master record.

needless to say that I'm the only user using the database. despite of the error message, ms access saves the record properly in the underlying table, but doesn't display it in the form (change to a different record, change back and it's there).

I suspect that there's a background process, scanning the titles table for records matching my (typed-in) selection criteria which conflicts with the insertion of the new record into the person_on_movie table.

I already tried to compress and repair the database, but it didn't help.

Any ideas how to overcome this problem ? maybe by implementing the lookup manually, in VBA ? (reducing the number of records is not an option ;-) )

tnx in advance.

View Replies


ADVERTISEMENT

Problems Using A Combo Box To Filter Records In Another Combo Or A Sub-form

Nov 8, 2004

I have seen a few articles here and there on using a combo box to filter records in a sub-form and to filter records in another combo box, but I am not getting anywhere. I hope someone can belp

Exercise 1

For this exercise, I have the following tables:

tblClients containing client names
tblProjects containing some project details

I want to set up a simple form, so that the user can select a client name from a combo box on the main form which filters the project detail records in the Projects sub-form.
Once the user enters project details, I want this info as well as the selected ClientID to be fed back to tblProjects.


Exercise 2

I have the following tables:

tblProjects as above
tblWorkstream containiig names of workstreams and some other details.

Every project has one or more workstreams

I have a form where users will enter hours worked on each workstream. They will first select Project from a combo box on the main form. This should then filter records to be displayed in the Workstream Combo box, before they can then enter hours.



Please let me know if you need me to explain any part of this better.

Thanks in advance.

ps. I am a novice, so I'm hoping to do the above using default MS stuff, not with complex programming!!!

Thanks

View 14 Replies View Related

Combo Box To Return Records In 2nd Combo Box!

Apr 3, 2008

Hi All. I'm Sure this is simple enough, but i just cant seem to figure it out.

I want to use a Combo dropdown box with Codes FC000 - FC900. to return records in a second drop down box that begins with the Letters selected in the first dropdown box.

So When a Record is Selected in ComboBox1 eg: (FC0**), ComboBox2 brings up the record to match (FC000).

eg:
ComboBox1: ComboBox2:
FC0**FC000
FC010
FC020

I hope i have expressed what i want to do.

Any Help would be greatly appreciated.

View 9 Replies View Related

Selecting Records Using Combo Box

Feb 16, 2005

Hi,

ok part of my database is made of 2 tables.

tblItems
item_id
maunfacturer_id
supplier_id
item_name

tblQuantities
quantity_id_num
item_id
quantity

(item_id linked)


ok so I want a form that has a combo box with [item_id, item_name] on each row that i select. when i select one it displays the relevant quantity and allows me to edit the quantity.

any ideas how i can do this?

Thanks:)

View 3 Replies View Related

Adding Records To A Combo Box

May 17, 2005

Hi all,

I have a data entry screen that requires the user to select a assessment type from a combo box that gets its values from a separate table. So far i have 3 Assessment types, these are Quality, Manufacturing and Technical.

If the assessment type they want is not is the combo box list. I want them to be able to type in the assessement type in the combo box and have it saved to the assessment type list so that in future it will be availible directly from the combo box.

Any ideas on how i can do this as it's giving me a head ache....

Many thanks in advance.

El Cid

View 8 Replies View Related

Using A Combo Box To Change Records

May 16, 2006

Hi all,

I was wondering how would we change the forms row source everytime a new new item is selected in a combo box.

For instance here is a table:

ID Name Status
1 Joe Active
2 Fred Inactive
3 Sam Inactive

So if the names were to be displayed in a combo box and the status is represented by a option group. How could I make the combo box change the record instead of having to use the record selector

Any help would be appreciated

thanks

View 3 Replies View Related

Combo Box Won't Go To Selected Records

Nov 5, 2004

I am having trouble getting a combo box to go to a selected record on a form. It basically ignores the selection. I have successfully used the combo box to do searches on other forms. The only difference I'm noticing is that form I'm using has multiple records for the search criteria.

Any ideas?

View 1 Replies View Related

Filtering Records With A Combo Box?

Dec 18, 2011

how I can modify this code to make it filter results in a combo box by what the user types in to search for.

Private Sub txtSearch_AfterUpdate()
With Me.RecordsetClone
.FindFirst "[FirstName]=""" & Me.txtSearch & """ OR [Lastname]=""" & Me.txtSearch & """"
If .NoMatch Then
Beep

[code]....

View 1 Replies View Related

Deleting Records From A Form Using Combo Box

Mar 11, 2008

What code do I need to delete a record using a form and a combo box. I need to be able to link the selection in the combo box to a button that, when pressed, will delete the selected record.

Information:

Account Executive Combo Box - select an account executive based on a table (that contains only account executives and an autonumber)

Table name: AccountExecutives
Form name: DeleteAccountExecutive
Combo Box name: Combo300
Button name: DeleteExecutive

View 1 Replies View Related

No Selection In Combo Box Shows All Records

Feb 9, 2006

I know this is probably posted in a dozen places, but I don’t think I have the smarts to find stuff with the search.

I use the results of a combo box for the criteria in a saved query. Can anyone tell me how to make it show all the record if no selection is made?

Thanks again Folks,
Joe

View 2 Replies View Related

Null Combo Box; Show All Records

Feb 11, 2006

Hello All,

I’m using a combo box for the criteria for a saved query. Is there any way to show all the records if the combo box value is either “0” or null?

Thanks,

View 4 Replies View Related

Combo Box Populates All Form Records

Nov 24, 2006

Hi everyone,

I have a form with a combo box, whose afterupdate code is:

Private Sub CustomerName_AfterUpdate()

Dim rs As Recordset
Dim person As String
{This will contain the SQL Statement}
person = "select people.department from people where people.name='" &

CustomerName.Value & "'"
Set rs = CurrentDb.OpenRecordset(person)
{Now the following are combo/text boxes that are populated}
Department.Value = rs("department")
InputDate = Date
InputTime = Time

My code works: when you select something in CustomerName combo box, the other

boxes in the record get populated with data. The problem is that ALL the records in the

form get populated with this data as well...
I want the records to be independent of each other - that each record will be filled

separately.

Any ideas?

Thanks,

Gilad.

View 1 Replies View Related

Return All Records If Combo Box Is Null

Jul 30, 2007

I have a form that using a combo box to select specific record, sometimes we need to see all records, i would like if the combo box if left empty to return all records. I typed this expression but it returns no records when combo box is empty.

IIf([Forms]![FrmReports]![LoanOfficer]="IsNull","*",[Forms]![FrmReports]![LoanOfficer])

If i use this expression i get the wrong results when a Loan Officer ID is enterd;
LIke([Forms]![FrmReports]![LoanOfficer] & *
Example of wrong results, if Loan officer Id is 1, then it returns loan officers 1,11, 111

Thanks in advance for any help

View 6 Replies View Related

Combo Box Blank When No Records On Form

Dec 30, 2005

Hello,

I have an unbound combo box in the header of a continuous form. The box is set to value list, and when you select a value, the recordsource of the form is changed. This works fine.

However, if I there are no records in the underlying form, the combo box is blank. If I check the value in the immediate window or the after update, I can see that it is set properly and contains a value, it just doesn't display it. I am using Access 2000. I have tested on Access 2003 and cannot replicate the problem.

I have found one post here (http://www.access-programmers.co.uk/forums/showthread.php?t=84423&highlight=combo+box+blank) on the topic.

I found this (http://support.microsoft.com/default.aspx?scid=kb;en-us;287478) article on Microsoft which has my symptoms, but I am not opening a recordset.

Anyone know how to fix this??

View 5 Replies View Related

Need Help On Filtering Records Using Combo Or List Box

Feb 18, 2006

Hi, I need help on my search command. I am hoping to be able to use a combo box or a list box coming from a table as my criteria to use to filter records from a form and present it a subform/subreport upon clicking the command button. Ideally I should have a form wherein I will have a either a combo box or list box for my criteria, a subform/subreport, and a command button. When I select a particular item on the combo box or list box and I click the command button, the subform/subreport would show me records matching only the particular criteria I selected.

I tried using several approach but it's not working, I don't know what I'm doing wrong. Please help me, I am just learning how to do this all by myself.

First Approach:
I tried using a list box to list all the countries I have available from the country table and a command button so when I select a country from the list box and click on the command button I will be able to show on a datasheet view only records matching the country criteria.
This is the code I used:
__________________________________________________ _______________
Private Sub Preview_Click()

DoCmd.OpenForm "qrysumcountry subform", , "Country", "Country = [List4]"

End Sub
__________________________________________________ _______________
But everytime I click on the command button Preview, I am always asked to enter parmeter value then when I type the country that's when it shows the record in forms format matching the criteria country but when I dont type anything and click ok, it just shows a blank form and indicates it's filtered but no record is showing. But I click cancel, it shows a Run-time error '2501'. why does it still have to make me type the parameter if I have selected it on the list box already?

Second Approach:
On the form: I used a combo box, a subform/subreport and a command button. On the combo box I have to show different countries available on my country table. on the subform/subreport I have used my a form created from a query. I want to select from the combo box a particular country and used it as my criteria to filter the records I have on my subform when I click on the search command button. I tried following the sample given by gromit but it doesn't want to work on my database.

This is the code I followed:
__________________________________________________ _______________
Private Sub btnClear_Click()
Dim intIndex As Integer
Me.cmbCountry = 0

End Sub

Private Sub btnsearch_Click()
Me.frmqrybyCountry1.Form.RecordSource = "SELECT * FROM qrybycountry" & BuildFilter

Me.frmqrybyCountry1.Requery

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant

varWhere = Null ' Main filter


'Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function
__________________________________________________ _______________
After selecting on the combo box and click on the command button it just shows all record. It doesn't seem like it is reading what I selected from the combo box as my criteria to filter the records. What could be I be doing wrong? Honestly, I dont understand what is happening to the code here especially the BuildFilter function.

Please help me, I would really want to figure this problem out. Thank you so much.

View 2 Replies View Related

Search For Records In A Form Via Combo Box

May 30, 2006

Dear all,

I'm trying to incorporate a combo box in my form to navigate through records. The plan is to select a piece of equipment listed in the combobox, and that record will then be displayed as an "after update" action.

However, I have tried through coding I have tried before and utilising the wizard to create this combo-box, but each time I get a "Complie Error - Can't find project or library".

The section of code is shown below:

Private Sub Combo227_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[EquipmentID] = " & Str(Nz(Me![Combo227], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Any help gratefully received!

Thanks,

Steve

View 2 Replies View Related

Displaying Records Dependant On Combo Box

Jun 8, 2006

Hi

Any ideas on how to autofill the rest of a form when i select from a combo box?

ie when i choose a name from the list i want the rest of the form to populate with the rest of the data relevant to the selection.

Cheers

View 2 Replies View Related

Cascading Combo Box Changing Records

Jul 13, 2006

I have attached a Microsoft inventory db that I have attempted to alter and apply to my needs.

I added cascading combo boxes to allow the users to make selections of products specific to categories. Once the category is selected in the CategoryID combobox the cascaded combo boxes ProductName & SerialNumber become populated with the products that belong to the category selected in CategoryID. This works as it should.

The problem comes after a record is selected and an attempt to switch categories is made. For example, say an inventory transaction for the product named "V1" which belongs to the "Special" category is entered. As soon as I switch back to the "Routine" category with the combobox, the product "V1" is now associated with the "Routine" category.

I know Access is just doing what it's supposed to but I need to find out how to prevent the category information for products from changing when a new category is selected in the first combobox. I've tried requeries, gotoRecords and various other commands without luck.

I have attached my db in hopes that a solution can be reached more efficiently.

Thanks in advance.

Shane

View 6 Replies View Related

Show Only Certain Related Records In Combo Box

Oct 14, 2006

I have a feeling this is a common and probably easy question, but I couldn't find anything on it in the forums.

I have a main customer form with multiple subforms on it to keep track of my clients insurance policies and claims. I enter their contact data in the main form, and the policy data on one of the subforms. Then when they have a claim, I enter the policy information on the claim subform as well.

How could I have the combo boxes in the claims subform (which is storing data in a claims table) only show the policy numbers (stored in a policies table) assigned to that particular person? I would assume I need to somehow filter the policies query so it only shows records for that particular person, but I'm not sure how to do that on the fly.

View 6 Replies View Related

Selecting Multiple Records From A Combo Box?

Jun 15, 2006

Is it possible to have a combo box on a form and allow users to select multiple entries from the drop-down list? Thats just the tip of the iceburg. If you can, then I'll probably be asking more questions soon.

Thanks in advance.

View 2 Replies View Related

Combo Box Populates All Form Records

Nov 24, 2006

Hi everyone,

I have a form with a combo box, whose afterupdate code is:

Private Sub CustomerName_AfterUpdate()

Dim rs As Recordset
Dim person As String
{This will contain the SQL Statement}
person = "select people.department from people where people.name='" & CustomerName.Value & "'"
Set rs = CurrentDb.OpenRecordset(person)
{Now the following are combo/text boxes that are populated}
Department.Value = rs("department")
InputDate = Date
InputTime = Time

My code works: when you select something in CustomerName combo box, the other boxes in the record get populated with data. The problem is that ALL the records in the form get populated with this data as well...
I want the records to be independent of each other - that each record will be filled separately.

Any ideas?

Thanks,

Gilad.

View 1 Replies View Related

Forms :: Duplicate Records In Combo Box

Mar 10, 2015

I have an issue with duplicate records showing in a combo box. I have checked "Yes" for unique values and they still show. I only have one field showing in the drop down and don't care about the other fields for that specific selection.

How to remove these duplicates from showing?

View 14 Replies View Related

Forms :: Omitting Records From A Combo Box?

Aug 3, 2015

I have an update form where a fail is displayed. The user can, using a combo box, change that fail.They can also add new records and pick the fail from the combo box.

The form is bound to query1 and the control source for the combo box is a field from this query which effectively come from a table, table1 The row source is another query, query2, that displays all fails.This works perfectly fine.

I now have a requirement not to display fails that have become redundant in the combo box (row source) but want to display them from query1 (control source). If I filter out the redundant fails in query2 then those that are on table1 are not displayed.

I presume this is because they don't appear in query2.

View 6 Replies View Related

Forms :: Hide Records That Have 1 In Combo Box

Jun 10, 2013

I have a form with a checkbox and a combo box. When I click the checkbox, I would like it to only show the specified records that are in the combo box. The current code I have is this:

Code:

Private Sub chkHideComplete_AfterUpdate()
On Error Resume Next
If Me.chkHideComplete = True Then
Me.filter = "[ReservationStatus] = 3"
Me.FilterOn = False

[Code] ....

I basically want to hide all records that have "1" in the combo box.

View 2 Replies View Related

Combo Box To Filter Records In A Subform

Aug 14, 2014

I have a combo box located on one of my forms, that is getting its information from a field in a table.This table has all its records displayed in a subform. I would like the combo box to basically requery the subform to only show records where the name matches what is in the combo box.

It is basically a list of customers, so if I was to select Person1 in the combo box, I would only want to see records for Person1 in the subform.I would also need a way of clearing the search results, in order to get the subform to display all records again.

View 9 Replies View Related

Combo Boxes Filtering Or Populting Same Records

Jun 8, 2005

Anyone help
I have 2 combo boxes cd group and cd name when I select CD group the records only associated yo that cd name display with that group.

Anyway, also the name of songs form works too,. So then now when I go from record to record the records display right with the songs and not with the cd group and name when I select Mormon Tabernacle Chior and name God Bless Ammerica it disaplys for all the records any help.

Why is it the relationships.

Thanks,

Nike Van der stad
mikevds@optonline.net

View 2 Replies View Related







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