creating a vba code to display data in a form access?

[jmasm]

I'm trying to create a VBA code that allows me to display a data of SQL database to a form.
I'm using this code to access and display data:

Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim rsPDM As ADODB.Recordset
Dim strPDM As String
Set rsPDM = New ADODB.Recordset
strPDM = "select * From PDM Where Ugf = 'Centro Litural';"
rsPDM.Open strPDM, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
i = 1
Do While Not rsPDM.EOF
If i = DLookup("ID", "PDM", "ID =" & i) Then
Me.ID = rsPDM!ID
rsPDM.MoveNext
End If
i = i + 1
Loop
rsPDM.Close
Set rsPDM = Nothing
End Sub

In the form appears in all the camps the last Id of the database.

 

[SkipVought]

Hi,

If you're doing this in MS Access, why not just bind the query to a form -- NO VBA REQUIRED!??? Forum702!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmasm]

I need to see some data structured by UGF!
Exemples:
Ugf = 'Centro Litural'
Ugf = 'Pinhal interior Norte'

This is more than 25 UGF and I do not want to create 25 Forms
Is there any way to create a query that i use a text box for select the Ugf?

 

[SkipVought]

Use a ComboBox of unique UGF values and use the result of the selection as the criteria for you query.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmasm]

When I do that an error pops up!
You can send me the steps to do this correctly?

 

[SkipVought]

Explain exactly what you did and exactly what error occured after your did WHAT?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmasm]

I created a query, the form.
After this step only made mistakes.
I tried to assign the data (UGF) directly in the query - error
I tried to use a when statement using the text box - error
I tried to use a macro - error
Can You send an example to put me on the right track.

 

[SkipVought]

post your QUERY.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmasm]

My query is this:

SELECT ID, Municipio, DataEntrAFN, Dataactual, Atualidade, Ugf

FROM PDM

WHERE (Ugf = [Forms]![Form13]![UGF])

My database is a SQL database that i access by ado.

 

[SkipVought]

In your ADO code...

'assign your SQL to a variable using single quotes to delimit the criteia variable
dim sSQL as string

strPDM = "SELECT ID, Municipio, DataEntrAFN, Dataactual, Atualidade, Ugf "
strPDM = strPDM & "FROM PDM "
strPDM = strPDM & "WHERE Ugf = '" & [Forms]![Form13]![UGF] & "'"

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[jmasm]

works but can not submit data in a form.
Can you tell me how I can put the data in the form (VBA).
I am using the following code:


Do While Not rsPDM.EOF
If i = DLookup("ID", "PDM", "ID =" & i) Then
Me.ID = rsPDM!ID
rsPDM.MoveNext
End If
i = i + 1
Loop

In the form appears only the lest record.
Thanks for the help...