INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ADODB FIND RECORD

ADODB FIND RECORD

(OP)
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 :)

RE: ADODB FIND RECORD

If CustID is numeric, try:

CODE --> vba

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

Duane
Hook'D on Access
MS Access MVP

RE: ADODB FIND RECORD

(OP)
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?

RE: ADODB FIND RECORD

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

RE: ADODB FIND RECORD

(OP)
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.

RE: ADODB FIND RECORD

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

CODE --> SQL

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

Then you can set the Control Source of CustomerName to:

CODE --> ControlSource

=CustID.Column(2) 



Duane
Hook'D on Access
MS Access MVP

RE: ADODB FIND RECORD

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

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

Duane
Hook'D on Access
MS Access MVP

RE: ADODB FIND RECORD

(OP)
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...

RE: ADODB FIND RECORD

>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. smile

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: ADODB FIND RECORD

(OP)
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.

RE: ADODB FIND RECORD

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).

bigsmile

Darrylles


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close