Message box & Auto expand Problem

[inklady]

Hi,

I'm learning Access 2003 through trial and error.. (little more error than trial I've noticed..)..

I have a form, where you type what you are looking for into the combo box hit "go" and it displays the information in various text boxes. 2 problems..

1 - I would like the combo box to auto expand, attempting to "auto fill" in what the user is typing - I have auto expand as yes and it does nothing

2 - if the combo box is empty and the user hits "go" I would like a message box to pop up and ask the user to input something.

Code thus far:

Option Compare Database
Private Sub Combo207_Click()

'Declare temporary variables
Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, temp9, temp10, temp11

'Make sure that something was entered in the search field
If IsNull(temp1) Then
MsgBox ("Please enter a search item")
End If

'Read the value from the machine number textbox into a temporary variable
temp1 = Combo207.Value

'Lookup the PTZ from the table
temp2 = DLookup("[ptz_num]", "machine_data", "[machine_num] = '" & temp1 & "'")

'Lookup the Asset from the table
temp3 = DLookup("[asset_num]", "machine_data", "[machine_num] = '" & temp1 & "'")

'Lookup the fixed camera from the table
temp4 = DLookup("[fixed_num]", "machine_data", "[machine_num] = '" & temp1 & "'")

'Lookup the fixed camera from the table
temp5 = DLookup("[fixed_num]", "br_data", "[br_num] = '" & temp1 & "'")

'Lookup the PTZ from the table
temp6 = DLookup("[ptz_num]", "br_data", "[br_num] = '" & temp1 & "'")

'Lookup the phone number from the table
temp7 = DLookup("[phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Lookup the alt phone number from the table
temp8 = DLookup("[alt_phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Lookup the person from the table
temp9 = DLookup("[person_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Lookup the job title from the table
temp10 = DLookup("[depart_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Output the data to the textboxes
Text185.Value = temp2
Text181.Value = temp3
Text183.Value = temp4
Text187.Value = temp5
Text189.Value = temp6
Text193.Value = temp7
Text175.Value = temp9
Text191.Value = temp10
Text195.Value = temp8

End Sub

 

[John Big Booty]

What is the Row Source for your Combo? As the normal behaviour of a Combo is to predict what the user is typing unless there is no match in the Row Source.

It appears that this code is associated with the Combo Box On Click event rather than that of the Command button you allude to

However assuming that the combo resided on the same form as the fields you are populating then you could simply put the code in the Combo's On Change event.

Option Compare Database
Private Sub Combo207_Click()

'Declare temporary variables
Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, temp9, temp10, temp11 [COLOR="Red"]As String[/COLOR]

'Make sure that something was entered in the search field
If IsNull([COLOR="Red"]Me.Combo207[/COLOR]) Then
MsgBox "Please enter a search item"
[COLOR="Red"]Exit Sub[/COLOR]
End If

'Read the value from the machine number textbox into a temporary variable
temp1 = [COLOR="Red"]Me.[/COLOR]Combo207.Value 

'Lookup the PTZ from the table
temp2 = DLookup("[ptz_num]", "machine_data", "[machine_num] = '" & temp1 & "'")

'Lookup the Asset from the table
temp3 = DLookup("[asset_num]", "machine_data", "[machine_num] = '" & temp1 & "'")

'Lookup the fixed camera from the table
temp4 = DLookup("[fixed_num]", "machine_data", "[machine_num] = '" & temp1 & "'")

'Lookup the fixed camera from the table
temp5 = DLookup("[fixed_num]", "br_data", "[br_num] = '" & temp1 & "'")

'Lookup the PTZ from the table
temp6 = DLookup("[ptz_num]", "br_data", "[br_num] = '" & temp1 & "'")

'Lookup the phone number from the table
temp7 = DLookup("[phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Lookup the alt phone number from the table
temp8 = DLookup("[alt_phone_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Lookup the person from the table
temp9 = DLookup("[person_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Lookup the job title from the table
temp10 = DLookup("[depart_num]", "phone_data", "[depart_num] = '" & temp1 & "'")

'Output the data to the textboxes
[COLOR="Red"]Me.[/COLOR]Text185.Value = temp2
[COLOR="Red"]Me.[/COLOR]Text181.Value = temp3
[COLOR="red"]Me.[/COLOR]Text183.Value = temp4
[COLOR="red"]Me.[/COLOR]Text187.Value = temp5
[COLOR="red"]Me.[/COLOR]Text189.Value = temp6
[COLOR="red"]Me.[/COLOR]Text193.Value = temp7
[COLOR="red"]Me.[/COLOR]Text175.Value = temp9
[COLOR="red"]Me.[/COLOR]Text191.Value = temp10
[COLOR="red"]Me.[/COLOR]Text195.Value = temp8

End Sub

 

[inklady]

There are 3 separate tables that the information is being polled from.

To do the row source.. would it be:

SELECT [fieldname] FROM

??

 

[John Big Booty]

The Row Source of your Combo will be the Table or Query that is populating the Combo.

 

[inklady]

Ok.. but how do you write that for 3 separate tables?

 

[John Big Booty]

I think we're going to need to see a copy of your DB ('07 version or earlier, for preference), as things are starting to sound de-normalised

 

[inklady]

How do you upload a file?

 

[John Big Booty]

First up do a compact and repair on your DB, then put it in a Zip file.

Then in your next post use the Paper Clip icon at the top of the posting window to up load your zip file.

If your able to save the DB as an '03 version I'll be able to have a look straight away.

 

[inklady]

there is some sensitive data in my project.. I can't release it.. sorry..

 

[John Big Booty]

Make a copy of your DB, delete all the sensitive data an insert some dummy data so we can see how how things are supposed to look.

 

[inklady]

Its too time consuming to do.. I have hundreds of lines to alter.. I'll just keep looking for my answer..

 

[vbaInet]

inklady,

I hope you appreciate the fact that we are also volunteering our time and expertise on here as well. So if you are keen on us helping you out, make the effort of uploading a stripped down version of your db.

Perhaps JBB could upload his sample Search db so that you can follow his code/examples. DLookup() is a lazy function and your use of it is very ineffecient.

With regards your questions:

1. JBB has answered this already in his first post. It auto-fills if a match is found.
2. What and where is "Go"?

 

[inklady]

vbaInet..

No..

1. The question was partially answered
2. Command button