how to run a query in vba and copy data from on form to the othe 1

[alpinhasov]

Hello, I would like to know how to run a query in vba. In the code below I tried to create a query and run it but it just showed me the query in the message box.
Also if I have a form keep the values when I press a button which will take me to another form. The values which were in the first form will be then put automatically to the other form. Obviously the fields that will be copied are the same on the
two forms.
Private Sub insertData_Click()
Dim myconnection As ADODB.Connection
Set myconnection = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset
myrecordset.ActiveConnection = myconnection
myrecordset.Open "studdemo", , adOpenStatic, adLockOptimistic

Dim result As String
Dim minhag As String

minhag = "SELECT STDLASTN FROM STUDDEMO"

'DoCmd.RunSQL minhag

result = Me![txtMy] & " " & Me![txtMc]
MsgBox ("You have entered in My and Mc information " & result)
MsgBox ("The full name is " & Me![fullname])
MsgBox ("The students cuastom is " & minhag)


Dim myQuery As String

'myQuery = "Insert into MINYAN (date, fullname, My, Mc ) Values Me![date], Me![fullname], Me![txtMy], Me![txtMc]"
'DoCmd.RunSQL myQuery

End Sub

 

[JerryKlmns]

Hi alpinhasov,

If I unterstood it corectly:

A] You want to have a specific recordset to been shown in formA
B] press a button to process
C] the same data in the other form: formB?

Then

A] Create formA with all the fields you need. At the properties of the form, on the tab Data, click the Record Source field and press the button [blue]...[/blue] There create the sql (as you would in a query and even test it for the result), to retrieve the recordset you need.

B] Add a button on the formA. On the properties -->click event press the button [blue]...[/blue] and put the following code

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "formB"

stLinkCriteria = "[fieldPK]=" & formA![PK]
DoCmd.OpenForm stDocName, , , stLinkCriteria

where PK is a field of the recordset returned from the sql yoy have created, that uniquely indentifies that record (used as Primary Key)

C]Copy formA and give the copy the name formB. Add the extra fields needed. At the Record Source of formB create the sql to retrieve the new recordset that contains the fiedlPK. This last field is filtered when formB is opened when the button on formA is clicked.

Give it a try and post back for more advice ...

 

[1DMF]

are you trying to get the result of the query into the msgbox by trying to display (minhag).

All you are displaying is a string
MsgBox ("The students cuastom is " & minhag)

you need to dimention a variable to capture the result of the query

 Dim rs As DAO.Recordset
 Dim sResult as String

Set rs = CurrentDb.OpenRecordset("SELECT STDLASTN FROM STUDDEMO", dbOpenDynaset, dbSeeChanges)

Do While Not rs.EOF
          sResult = sResult & rs.Fields("Field Name Required") & vbcrlf

Loop

msgbox sResult

something like that will give you the result of the query

 

[koskal]

something like this is missing from the do while loop statement: rs.MoveNext

 

[ProgramError]

Try putting the query you intend running in the record source of the second form. It will run the query when the form is opened, by a command button perhaps.

Ian M (UK)

Program Error
Programmers do it one finger at a time!

 

[alpinhasov]

Thanks everybody.

 

[1DMF]

nice spot koskal

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.