Find a Record

[Quique79]

Hi!!

I need to find the IDCompany depending on user selection that matches with the ID

For Example

Text1.Text= "SCA" 'User selection


and I need to obtain the CompanyID that is in the same table (Access Database) in a Text Box for further use

IDCompany CompanyName
1 GNP
2 STE
3 SCA
4 ABS

Is there a way to do it with code?

Any hit or tip could be helpful

Quique79

 

[Imhoteb]

Hi,
try this please

Private myConn As New ADODB.Connection
Private myRS As New ADODB.Recordset

Dim Result as Integer
myConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\myDB.mdb"
myRS.Open "SELECT * From myTable where CompanyName ='" & Text1 & "'", myConn, adOpenStatic, adLockPessimistic
myRS.Requery
With myRs
If Not .EOF then
.MoveFirst
Result = !IDCompany
MsgBox "IDCompany for " & Text1 & " = " & Result
Else
MsgBox "No recors found"
End IF
End With
myRS.Close
myConn.Close

Hope this helps
Good luck

 

[MikeCox]

Here's another way if you already have a recordset that contains the value you're trying to search for:
-------------------------------
'Explicit search
Adodc1.Recordset.Find "IDCCompany = '" & Text1.Text & "'", , adSearchForward, 1

'Use wildcard, begins with...
Adodc1.Recordset.Find "IDCCompany = '" & Text1.Text & "*'", , adSearchForward, 1

'Use wildcard, containing...
Adodc1.Recordset.Find "IDCCompany = '*" & Text1.Text & "*'", , adSearchForward, 1
--------------------------------

HTH.

~Mike
Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin

 

[Quique79]

Hey guys... thanks for your help... I have another question, how can I fill the form's text boxes with the record found

Any hint or tip?

Thanks

Quique79

 

[Imhoteb]

hi,
you can try this

With myRs
If Not .EOF then
.MoveFirst
txtIDCompagny = !IDCompany
txtCompanyName = !CompanyName
End IF
End With

 

[MikeCox]

The easiest way is to use a control array of textboxes (called txtFields here):

-----------------------------------
Dim RcdCtr As Long

With Adodc1.Recordset
For RcdCtr = 0 to .Fields.Count - 1
If IsNull(.Fields(RcdCtr).Value) = False Then
txtFields(RcdCtr).Text = .Fields(RcdCtr).Value 'error if null
Else
txtFields(RcdCtr).Text = ""
End If
Next RcdCtr
End With

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

Good luck!

~Mike

Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin