ADODB FIND RECORD

[humpydumpy2000]

hi, i am using ADODB to find records in a table that matches criteria in a textbox. It works fine except when I try to use it to find an ID field (primary key)

Dim rsFname As New ADODB.Recordset

rsFname.Open "SELECT * FROM tblCustomers " & _
"WHERE CustomerID = '" & CustID & "';", _
CodeProject.Connection, _
adOpenStatic, _
adLockOptimistic, _
adCmdText

With rsFname
Do While Not .EOF
If rsFname("CustID").Value = CustomerID Then
CustomerName = .Fields("Custname").Value
Exit Do
End If
.MoveNext
Loop
End With

Any help is greatly appreciated. Thanks in advance

 

[dhookom]

If CustID is numeric, try:

rsFname.Open "SELECT * FROM tblCustomers " & _
"WHERE CustomerID = " & CustID & " ;", _

Duane
Hook'D on Access
MS Access MVP

 

[humpydumpy2000]

Hi, there is no error but nothing happens. To better explain it I have a table called tblCustomers

----------------------------------
CustID | CustNumber | Name
----------------------------------
1 | 205 | HH

When I used the code above to get customer info with CustNumber as reference to search the data I get no problems, but I am clueless what is wrong why it does not work with primary keys (CustID). Can you please suggest a better code so I can load customer info in a form if my reference is the CustID?

 

[dhookom]

I'm confused regarding the difference in field names between your code and your table.

Can you simply select the CustID from a combo box? This is the typical solution which would allow using the Column(x) value of the combo box to populate another control.

We also don't know what your larger requirement is. Is this a search form or are you attempting to store the customer name in a bound text box?


Duane
Hook'D on Access
MS Access MVP

 

[humpydumpy2000]

I am using an unbound form, it just so happen that it has multiple tabs and the customerID name is already taken, that explains why I have to use CustID as a control name of combo box which after update I would like some ubound textboxes to be populated with data from customer table.

 

[dhookom]

I expect you are using too much effort. If the Row Source of the CustID combo box is:

SELECT CustID, CustNumber, [Name] FROM tblCustomers ORDER BY CustID

Then you can set the Control Source of CustomerName to:

=CustID.Column(2)

Duane
Hook'D on Access
MS Access MVP

 

[humpydumpy2000]

Please help me fix these. Thanks in advance.

 

[dhookom]

Change the CustID properties:
[pre]Row Source: SELECT tblCustomers.CustomerID, tblCustomers.CustomerName, tblCustomers.CustNumber,
tblCustomers.CustomerAddress, tblCustomers.Phone FROM tblCustomers;
Column Count: 5[/pre]

Then change the other text box control sources to:
[pre]=[CustID].[column](1)
=[CustID].[column](2)
=[CustID].[column](3)
=[CustID].[column](4)
[/pre]
Get rid of the code.

Duane
Hook'D on Access
MS Access MVP

 

[humpydumpy2000]

If that's the case I would prefer to use listbox instead.

me.custname = me.lstbox.column(1)
me.customernumber = me.lstbox.column(2)
etc...

 

[Andrzejek]

>I would prefer to use listbox instead
Why use listbox (or combobox) when you are going to show, the most, just 1 record? Since
>to use it to find an ID field (primary key)
PK points to just one record in a table.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[humpydumpy2000]

What I would like to solve to load record from a table to an unbound form. Using the code I posted at the beginning of this thread I was able to search related records loaded into form using customer number as reference. However loading related records using primary key is rather difficult. Using listbox is a good option to show a bunch of records but not too many.

 

[Darrylles]

Prime example of why standard object naming convention is used.

In the above code the word 'CustomerID' is referred-to as a table field AND a form object.
Now which is which?
Easy for the compiler to differentiate, but difficult for us mere humans to do the same.

Code so that HUMANS can read it: simple!

E.g. the field on the form:
txtCustomerId
cmbCustomerId
lstCustomerId

(But obviously not simple enough for some).



Darrylles