Click Even

donq
11-03-2004, 07:50 PM
Greetings:

I'm loading a combo box with a list of account numbers & customer names I would like to have the user select one and then open an edit session with the particular record selected.

Each combo entry looks like:

123456 - Abc Co
654321 - GMAC
246833 - My Business.com
etc

I'm using the combo click event to try and retrieve the key (first six positions) of the dropdon entry.

My question is how do I pass this string to the click event or what is the appropriate way to achieve editing of the record that was clicked on?

This method is not working, Could someone shed some light on what's needed?

Again all I'm trying to accomplish here is when the user clicks on an entry is to retrieve that record.

As usual I thank you guys in advance,

Donald

Dennis DVR
11-03-2004, 09:46 PM
Greetings:

I'm loading a combo box with a list of account numbers & customer names I would like to have the user select one and then open an edit session with the particular record selected.

Each combo entry looks like:

123456 - Abc Co
654321 - GMAC
246833 - My Business.com
etc

I'm using the combo click event to try and retrieve the key (first six positions) of the dropdon entry.

My question is how do I pass this string to the click event or what is the appropriate way to achieve editing of the record that was clicked on?

This method is not working, Could someone shed some light on what's needed?

Again all I'm trying to accomplish here is when the user clicks on an entry is to retrieve that record.

As usual I thank you guys in advance,

Donald

Hi Donald

What do you mean by retrieve that record? do you want to retrieve all the information associated with that key? and you say it's not working, do you receive any error or the output is not correct? maybe we can fix the problem if you're going to post the code that you are currently using.

NEOLLE
11-03-2004, 09:49 PM
..retrieve the key (first six positions) of the dropdon entry.

My question is how do I pass this string to the click event or what is the appropriate way to achieve editing of the record that was clicked on?

Hi Donald,
The way I see it your on the right path. You can use the ComboBox.Text property to return the value of the ComboBox clicked.

To get the first six positions or characters use the Left$ function to attain this. Here is a sample code :)


Private Sub Command1_Click()
With Combo1
.Clear
.AddItem "1234567890"
.ListIndex = 0
MsgBox Left$(.Text, 6)
End With
End Sub

donq
11-04-2004, 12:28 AM
Hi duane & NEOLLE, thanks for your reply.


NEOLLE I tried your approach and I'm now getting
the following error:

run-time error 3001

Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another.


As follows is the code and where the error pops up:

Private Sub Combo4_Click()

txt_Customer_no = (Left$(Combo4.Text, 6)


'also tried this >>>> txt_Customer_no = Int(Left$(Combo4.Text, 6))


Dim adoConn As adodb.Connection
Set adoConn = New adodb.Connection

adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=\\Megaone\rental.mdb; Persist Security Info=False "

Set adoCustomerRS = New adodb.Recordset

Dim RsSelect As String
RsSelect = "SELECT * from customers where customerNo = " & customer_no & " and CustomerStatus = 'New' "

DIES HERE >>>>>>> adoCustomerRS.Open RsSelect, adoConn, adOpenKeyset, adLockPessimistic, adCmdText

If (adoCustomerRS.RecordCount = 1) Then

txt_reservation_date.Text = adoCustomerRS.Fields("PurchaseDate")
txt_last_name.Text = adoCustomerRS.Fields("CustomerLastName")
txt_first_name.Text = adoCustomerRS.Fields("CustomerFirstName")
txt_phone.Text = adoCustomerRS.Fields("CustomerPhoneNo")

End If

adoCustomernRS.Close
Set adoCustomerRS = Nothing

End Sub

-----------------------------------------------------------------------------------------------


This is the lookup routine that works fine if a valid customer no is entered
basically the same as above. This one kicks off the above.


Public Sub cmd_Customer_lookup_Click()
Dim combined_text As String

If txt_Customer_no = "" Then
intmsgno2 = MsgBox("The Customer number should not be BLANK for a lookup", vbCritical, "Error")
txt_Customer_no.SetFocus
Exit Sub
End If

Dim adoConn As adodb.Connection
Set adoConn = New adodb.Connection

adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=\\Megaone\rental.mdb; Persist Security Info=False "

Dim adoCustomerRS As adodb.Recordset
Set adoCustomerRS = New adodb.Recordset
adoCustomerRS.CursorLocation = adUseClient

Dim RsSelect As String
RsSelect = "SELECT * from Customers where CustomerNo = " & txt_Customer_no & " and CustomerStatus = 'New' "

adoCustomerRS.Open RsSelect, adoConn, adOpenKeyset, adLockPessimistic, adCmdText

If (adoCustomerRS.RecordCount = 1) Then
txt_purchase_date.Text = adoCustomerRS.Fields("PurchaseDate")
txt_last_name.Text = adoCustomerRS.Fields("CustomerLastName")
txt_first_name.Text = adoCustomerRS.Fields("CustomerFirstName")
txt_phone.Text = adoCustomerRS.Fields("CustomerPhoneNo")

Else
adoCustomerRS.Close
Set adoCustomerRS = Nothing

Set adoCustomerRS = New adodb.Recordset

Combo4.Text = "Please Select An Entry"
intmsgno2 = MsgBox("Customer Number was NOT FOUND ON FILE. Please view the DROP DOWN list for a POSSIBLE MATCH...", vbInformation, "Customer Lookup")

RsSelect = "SELECT * from Customers where CustomerStatus = 'New' "
adoCustomerRS.Open RsSelect, adoConn, adOpenKeyset, adLockPessimistic, adCmdText

CustomerRS.MoveFirst
Do While Not adoCustomerRS.EOF
combined_text = adoCustomerRS("CustomerNo") & " - " & adoCustomerRS("CustomerLastName") & ", " & adoCustomerRS("CustomerFirstName")
Combo4.AddItem combined_text
adoCustomerRS.MoveNext
Loop
End If
adoCustomerRS.Close
Set adoCustomerRS = Nothing
End Sub

NEOLLE
11-04-2004, 12:47 AM
Hi donq :)
What datatype is your txt_Customer_no? Is this a textbox?

Check you Query Statement. Use you Immediate Window to view the value of your RsSelect.

You can use this instead:
adoCustomerRS.Open RsSelect, adoConn, adOpenForwardOnly,AdLockReadOnly

donq
11-04-2004, 01:03 AM
Hi donq :)
What datatype is your txt_Customer_no? Is this a textbox?

Check you Query Statement. Use you Immediate Window to view the value of your RsSelect.

You can use this instead:
adoCustomerRS.Open RsSelect, adoConn, adOpenForwardOnly,AdLockReadOnly


Hi NEOLLE:

The datatype of txt_Customer_no is general. I tried changing the open and lock, but I get the same error???

What I don't understand is that the same setup is used in the lookup routine with no problem? I stepping thru the code I can see a valid account number in txt_Customer_no, any other thoughts?

Thanks for the reply

NEOLLE
11-04-2004, 05:29 AM
What General Datatype? Variant you mean?
Can you post us a sample of your Query Statement? :)

donq
11-04-2004, 08:46 PM
Hi NEOLLE:

As follows the code:
run-time error 3001

Arguments are of the wrong type, are out of acceptable range,
or are in conflict with one another.


As follows is the code and where the error pops up:

Private Sub Combo4_Click()

txt_Customer_no = (Left$(Combo4.Text, 6)


'also tried this >>>> txt_Customer_no = Int(Left$(Combo4.Text, 6))


Dim adoConn As adodb.Connection
Set adoConn = New adodb.Connection

adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=\\Megaone\rental.mdb; Persist Security Info=False "

Set adoCustomerRS = New adodb.Recordset

Dim RsSelect As String
RsSelect = "SELECT * from customers where customerNo = " & customer_no & " and CustomerStatus = 'New' "

DIES HERE >>>>>>> adoCustomerRS.Open RsSelect, adoConn, adOpenKeyset, adLockPessimistic, adCmdText

If (adoCustomerRS.RecordCount = 1) Then

txt_reservation_date.Text = adoCustomerRS.Fields("PurchaseDate")
txt_last_name.Text = adoCustomerRS.Fields("CustomerLastName")
txt_first_name.Text = adoCustomerRS.Fields("CustomerFirstName")
txt_phone.Text = adoCustomerRS.Fields("CustomerPhoneNo")

End If

adoCustomernRS.Close
Set adoCustomerRS = Nothing

End Sub

-----------------------------------------------------------------------------------------------


This is the lookup routine that works fine if a valid customer no is entered
basically the same as above. This one kicks off the above.


Public Sub cmd_Customer_lookup_Click()
Dim combined_text As String

If txt_Customer_no = "" Then
intmsgno2 = MsgBox("The Customer number should not be BLANK for a lookup", vbCritical, "Error")
txt_Customer_no.SetFocus
Exit Sub
End If

Dim adoConn As adodb.Connection
Set adoConn = New adodb.Connection

adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
" Data Source=\\Megaone\rental.mdb; Persist Security Info=False "

Dim adoCustomerRS As adodb.Recordset
Set adoCustomerRS = New adodb.Recordset
adoCustomerRS.CursorLocation = adUseClient

Dim RsSelect As String
RsSelect = "SELECT * from Customers where CustomerNo = " & txt_Customer_no & " and CustomerStatus = 'New' "

adoCustomerRS.Open RsSelect, adoConn, adOpenKeyset, adLockPessimistic, adCmdText

If (adoCustomerRS.RecordCount = 1) Then
txt_purchase_date.Text = adoCustomerRS.Fields("PurchaseDate")
txt_last_name.Text = adoCustomerRS.Fields("CustomerLastName")
txt_first_name.Text = adoCustomerRS.Fields("CustomerFirstName")
txt_phone.Text = adoCustomerRS.Fields("CustomerPhoneNo")

Else
adoCustomerRS.Close
Set adoCustomerRS = Nothing

Set adoCustomerRS = New adodb.Recordset

Combo4.Text = "Please Select An Entry"
intmsgno2 = MsgBox("Customer Number was NOT FOUND ON FILE. Please view the DROP DOWN list for a POSSIBLE MATCH...", vbInformation, "Customer Lookup")

RsSelect = "SELECT * from Customers where CustomerStatus = 'New' "
adoCustomerRS.Open RsSelect, adoConn, adOpenKeyset, adLockPessimistic, adCmdText

CustomerRS.MoveFirst
Do While Not adoCustomerRS.EOF
combined_text = adoCustomerRS("CustomerNo") & " - " & adoCustomerRS("CustomerLastName") & ", " & adoCustomerRS("CustomerFirstName")
Combo4.AddItem combined_text
adoCustomerRS.MoveNext
Loop
End If
adoCustomerRS.Close
Set adoCustomerRS = Nothing
End Sub


Thanks for responding

NEOLLE
11-04-2004, 09:27 PM
I did some research on your error code and it leads me to this
Error 3001 (http://support.microsoft.com/default.aspx?scid=kb;en-us;216873)

That left function should work though.
Use the VB tags in posting Codes, it will look something like this

Dim strSample As String
'----Your code

:)

donq
11-04-2004, 10:56 PM
Thanks NEOLLE

Will give it a try.

Dennis DVR
11-05-2004, 05:03 AM
RsSelect = "SELECT * from customers where customerNo = " & customer_no & " and CustomerStatus = 'New' "

in your select you are using customer_no not txt_Customer_no, do you really have customer_no variable define? and are you sure it's not empty as in Empty not ""? and what its datatype?

Try putting Option Explicit in every form and module in your project.

donq
11-06-2004, 02:38 AM
RsSelect = "SELECT * from customers where customerNo = " & customer_no & " and CustomerStatus = 'New' "

in your select you are using customer_no not txt_Customer_no, do you really have customer_no variable define? and are you sure it's not empty as in Empty not ""? and what its datatype?

Try putting Option Explicit in every form and module in your project.



Thanks duane that's what it was: missing the txt_

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum