in ACCESS VBA, how do I search

QuantumCat
10-26-2004, 03:52 AM
I created a form and I want a button to
start a macro
This macro needs to find a report number in a table
(or a query, I have both) and after finding it, fill the form with the
data corresponding to that reportnumber.

How do I do that?

QC

sillyman
10-26-2004, 05:10 PM
Why do u need a macro?

In your prgramming source code you need a query basically if you had a textbox entered the report number you wanted to find and then when you click a command button

rs.Open "Select * From Users Where [ID]= '" & txtID.Text & "'", cnn, adOpenForwardOnly, adLockReadOnly

With rs
If Not rs.EOF Then
txtTitle.Text = rs.Fields("Report Title").Value

End If

I think my above code will help as txtID is equal to ID in database in the other textbox txtTitle it will display its other data.

I think thats what u want!

QuantumCat
10-26-2004, 05:24 PM
No it needs a macro or a button that starts an event.
This event is searching for a Report ID (or report number).

Once this record is found I want to fill a form with the data from this record.

From there I will export it to a report (in Access) and print the data

I understand that at some point I need to do something with select butI don't want a query as in Access. It needs to be indeed as your example below be a VB(A) code that will extract the data and then fill the form.

Now I am that far that I created a command button that does something.
This opens the macro instead of reading from it. I will use the where statement (i forgot that was an option) as you pointed out.
This piece of code is saved as a macro.

How do I read the rest? There are about an odd 30 columns to the record.
There must be a way to get that data without summing up all 30 items
in your "select ... from ...where" statement?

Thnx btw - I am a step closer :-)

QC
Why do u need a macro?

In your prgramming source code you need a query basically if you had a textbox entered the report number you wanted to find and then when you click a command button

rs.Open "Select * From Users Where [ID]= '" & txtID.Text & "'", cnn, adOpenForwardOnly, adLockReadOnly

With rs
If Not rs.EOF Then
txtTitle.Text = rs.Fields("Report Title").Value

End If

I think my above code will help as txtID is equal to ID in database in the other textbox txtTitle it will display its other data.

I think thats what u want!

sillyman
10-26-2004, 06:24 PM
I am a newbie myself so I dont really know but I think you will need to declear everything seprateley

either like

txtTitle.Text = rs.Fields("Report Title").Value
txtTitle2.Text = rs.Fields("Report Title2").Value
etc.

or:

rs.Open "Select * From Users Where [Username]= '" & txtUsername.Text & "' And [Privalliages]= '" & txtPriv.text & "' And [Password]= '" & txtPassword.Text & "'", cnn, adOpenForwardOnly, adLockReadOnly

Thats about as much as I know!

QuantumCat
10-26-2004, 06:31 PM
I am a newbie myself so I dont really know but I think you will need to declear everything seprateley

either like

txtTitle.Text = rs.Fields("Report Title").Value
txtTitle2.Text = rs.Fields("Report Title2").Value
etc.


hmm I was afraid of that.


or:

rs.Open "Select * From Users Where [Username]= '" & txtUsername.Text & "' And [Privalliages]= '" & txtPriv.text & "' And [Password]= '" & txtPassword.Text & "'", cnn, adOpenForwardOnly, adLockReadOnly

Thats about as much as I know!

And there is another thing -I'm in VBA now, so do I declare a recordset?
i'm a bit confused by the differences between VB and VBA so I'm not sure what is the same in either and what is similar, but not quite the same.

QC

QuantumCat
10-27-2004, 01:56 AM
This doesn't work, it says that it cannot connect to the recordset.
Of course, I didn't create a recordset (how do I do that in VBA, in VB it's "easy" -see code at the bottom). I assumed (=wishful thinking)
that I needn't do that since I am working a macro from the access file itself instead of working from an external VB program


Sub ZoekRecords()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim strSQL As String


Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset

'MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Secuity Info=False;Data Source=K:\Persoonlijke werkmappen\jaimy\ACCESS\test.mdb;"

'DoCmd.OpenTable ("WPS Invoer")

MyRecSet.Open "Select * From [WPS Invoer] WHERE [WPS invoer.WPS naam]= '" & Invoer & "'"
'strSQL = ("SELECT [WPS invoer.WPS naam], [WPS invoer.Lasnorm], [WPS invoer.Lasproces 1], [WPS invoer.Lasproces 2]," _
'& " [WPS invoer.Lasproces 2], [WPS invoer.Lasproces 3," & "[WPS invoer.Lasproces 4], [WPS invoer.Lasproces 5] ," _
'& " [WPS invoer.Basismateriaal A], " & " WPS invoer.Basismateriaal B]FROM [WPS Invoer]" & "WHERE [WPS Invoer.WPS naam] = '" & Invoer & "'")
With MyRecSet
If Not MyRecSet.EOF Then
Form_Formulier1.E01 = MyRecSet.Fields(1).Value
End If
End With
'MyRecSet.Open strSQL, MyConn, adOpenStatic, adLockReadOnly, adCmdText

End Sub


working VB code:


Public Sub accessSQL()
Dim MyConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Dim strSQL As String
Dim hulp1 As Long
Dim hulp2 As Long


Set MyConn = New ADODB.Connection
Set MyRecSet = New ADODB.Recordset
NDOfound = True

MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=F:\DATA\ConstructieDatabase\Databases-97.mdb;"

MyConn.Open

strSQL = ("SELECT [NDO Resultaat tabel.Ond-id ], [NDO Resultaat tabel.QDomschr], [NDO Resultaat tabel.NDO Nr], [NDO Resultaat tabel.Langsnaad]," _
& " [NDO Resultaat tabel.Rondnaad], [NDO Resultaat tabel.Onderzoek RN]," _
& "[NDO Resultaat tabel.Dikte Groep], [NDO Resultaat tabel.S] , [NDO Resultaat tabel.DN] FROM [NDO Resultaat tabel]" _
& "WHERE [NDO Resultaat tabel.Meetbuis Nummer] = '" & strArtikelNr & "'")

MyRecSet.Open strSQL, MyConn, adOpenStatic, adLockReadOnly, adCmdText
'&c
'&c
End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum