Slow action

[Kiwi-Wombat]

Hi

I have a pop-up form as part of my project which contains 4 unrelated tables. Each table has a AutoNumber as the key and one text field.

The record source for the form is

SELECT tblFractions.*, tblUnitsOfMeasure.*, tblIngredients.*, tblPreparation.* FROM tblFractions, tblUnitsOfMeasure, tblIngredients, tblPreparation;​

The form contains 4 list boxes. Each one has the relevant table/field as the table/query row source). The user double clicks on an item in any one of the list boxes which transfers the selected item to another field. This builds up a sentence which eventually is transfered to the original calling form.

The code for selecting the items is (note the variables are defined elsewhere):

Private Sub SelectItem_Click()

Dim rst As Recordset
Set rst = Me.RecordsetClone

If varFractions Then
rst.FindFirst "FractionsID = " & FractionsList
ElseIf varUnitMeasure Then
rst.FindFirst "UnitMeasureID = " & UnitMeasureList
ElseIf varIngredient Then
rst.FindFirst "IngredientID = " & IngredientsList
ElseIf varPreparation Then
rst.FindFirst "PreparationID = " & PreparationList
End If

Me.Bookmark = rst.Bookmark

If varFractions Then
InsertionString = InsertionString & Fraction & " "
ElseIf varUnitMeasure Then
InsertionString = InsertionString & UnitMeasure & " "
ElseIf varIngredient Then
InsertionString = InsertionString & Ingredient & " "
ElseIf varPreparation Then
InsertionString = InsertionString & Preparation & " "
End If

End Sub​

This works Ok but my difficulty is that although each table has only about 50 items, if you select the last item in the last table it takes at least 3 seconds to transfer the selection to the separate field. The more records you add the longer it takes.

If you add another table the the form's record source, the delay is such that the form 'hangs'.

The only reason for having the tables in the form's record source is that is the only way I can do a search using RecordsetClone.

Does anyone have another suggestion?

 

[Kiwi-Wombat]

That explains why you're the moderator and I'm asking the stupid questions.

I have removed the autonumber from the tables and selected the text field as the key because you're right, I don't use the tables except for a lookup and therefore don't need an autonumber as the ID key. It now works prefectly

Thanks for your valued assistance.

Out of curiosity, when I left the autonumber as the key and used

InsertionString = InsertionString & Me.FractionsList & " "​

the Insertion string only showed the ID number. If I deselect the ID record form the list box query, no record is shown in the list box. The only way I could do it was by removing the autonumber from the table. Can anyone explain why?

Thanks

 

[Kiwi-Wombat]

Thanks once again, your answers are always so helpful.

I will try what you suggested but in terms of good database management, is it acceptable to have a table with a single text field and have that field as the key field i.e. don't have an autonumber as the key field?

Obviously to be a key field the text would have to have no duplicates but apart from that are there any other considerations?

Thanks

 

[Kiwi-Wombat]

Thanks again