p2p.wrox.com Forums - Access VBA

Form Record Source

ayazhoda — Mon, 16 Nov 2009 11:29:30 GMT

Hi
I am using this this to fill up my continous form with different text field in it

Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim r As New ADODB.Recordset

cmd.ActiveConnection = SQLcon
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_Order"

Set prm = cmd.CreateParameter("@SDATE", adDBTimeStamp, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("@SDATE").Value = Format(Me!DateSearch, "yyyy/mm/dd hh:mm:ss")

Set prm = cmd.CreateParameter("@CUSER", adInteger, adParamInput)
cmd.Parameters.Append prm
cmd.Parameters("@CUSER").Value = CUser()
Set r = cmd.Execute()

'THIS LINE GIVE ERROR
'Set Me.Recordset = r

'THIS CODE BELOW ABLE TO PRING VALUES IN DEBUG SCREEN
While Not r.EOF
Debug.Print r!Rep & " , " & r!MID & " , " & r!TDate & " , " & r!Value & " , " & r!SDATE & " , " & r!ElecOrMach
r.MoveNext
Wend

any idea

Regards

Ayaz

SubForm Refresh Delay Problem

cheer — Fri, 06 Nov 2009 04:41:37 GMT

Created one main form contains a SubForm inside. There is two buttons on the main form, indicated by
a) Save button
b) Refresh button

This button will insert data through the insert SQL string into seperate MS Access but under the same server before the data is displayed on the SubForm.

Question:
Why I need to click the Refresh button 2 to 3 times in order to obtain the latest info ? Below is my code

Quote:

Private Sub RefreshSubForm()
DoCmd.Requery ("SubForm_V_QATransaction")
Me.Refresh
Me!SubForm_V_QATransaction.Form.Refresh
Me!SubForm_V_QATransaction.Form.Filter = "StartDate Is Null or EndDate Is Null"
Me!SubForm_V_QATransaction.Form.FilterOn = True
End Sub

Any chance not to allow the few clicks exercise, on the other hand, type in few VBA codes to act as timer for delay purpose like DoEvents for example .

(Tried before the DoEvents method, however, result remain the same)

VBA - Lookup

ppenn — Thu, 05 Nov 2009 09:01:17 GMT

Hi
My previous post about loop through datasets probably was not that clear

What I am trying to do is perform the same action as Excel Lookup function in VBA.

Where a field is searched and an associated field returned.

For example if a text box in a form shows a certain value that value is searched and if found an associated value returned to another text box.

Hope that might be clearer

Regards [:)]

Loop through fields

ppenn — Mon, 02 Nov 2009 19:06:56 GMT

Hi

I am reasonably familiar with the code to loop through dataset records and assign the values to variables, but I am currently stumped on something I am trying to do.
I have a form with various text boxes with points values in them and calculates a total of these points into another text box. I want to loop through the fields of a table and find a value that matches the total on the form, I then want to be able to display the result in another text box on the form.As an example imagine the table as follows
Field -- A--- B
Value -15- ABC
Value -25- DEF
Value -40- GHI

If the code loops through column A and if it finds the value on the form matches 25 then I want the value DEF to be able to be displayed on the form.
I thought that the code could run from the "After Update" event on a text box or combo box.
Thank you in advance, hoping you can understand my logic
Best regards

Data Cannot Displayed On SubForm Correctly

cheer — Fri, 30 Oct 2009 02:14:10 GMT

I have created one main form, under this main form, there is one subform (subform name: SubForm_Production_Schedule) and one button. When this button is clicked, I need the data to display on subform base on certain criteria. Below is the part of the code under the button click event

Quote:

Me!SubForm_Production_Schedule.SourceObject = "Table.Production_Schedule"

Me!SubForm_Production_Schedule.Form.RecordSource = "select * from Production_Schedule where Login='" & txtOperator.Value & "'"

Me!SubForm_Production_Schedule.Requery

Me.Refresh

Me.Repaint

However, there isn't any data appear after the button is clicked. When I search back the table Production_Schedule, in fact there are more than 100 rows of data available.

Anyone can advise the what goes wrong to the above coding ? I am using the MS Access 2000.

DAO

lmcc007 — Sat, 24 Oct 2009 18:39:48 GMT

I am a novice and trying to learn VB; therefore, I purchased this book. I am on Chapter 6: Using DAO to Access Data, page 111. I am not understanding it. Anyway, I am trying to understand some code I keep seeing, for example:

Dim dbMyDB As DAO.Database
Set dbMyDB = DBEngine.Workspace(0).Databases(0)

The explanations I have read are not clear to me.

What is DAO and workspace? I need non-technical explanations or explanations explained simple enough that a dummy can understand it. I need a clearer understanding of DAO.

My thoughts right now are: DAO is like a library that holds templates to do this and that to your data--for instance, to delete a record, the code is store in a DAO library for delete--you have to go to that library, retrieve the template for delete, save the template to your document, and then use the template to perform a deletion.

Any simple explanations and/or examples are appreciated.

Thanks!

Run-time error '3296': Join expression not supported.

ashley.baker — Fri, 23 Oct 2009 13:23:17 GMT

Hello,
Please could someone help me?
I have built 2 Access queries, one is a select query that gathers all of the data required and the second is a crosstab that uses the results from the first query and populates it into the correct format needed (I.E. all of the dates as column headers)
Both of these queries run fine in Access but when trying to put them into a module I am getting the "Run-time error '3296': Join expression not supported." (Bit of code below in BOLD)
Here is the code used in the module:
As you can see I have put in a Do While loop in to run the queries using 'Agent' as a parameter.
If anyone can help I would be very grafetful as I know have no hair and no skin left on my teeth!

Thanks

Code:

Option Compare Database

Option Explicit

Sub Productivity_Daily_Report()



Dim rsResources As Recordset

Dim rsFirst As Recordset

Dim rsSecond As Recordset



Dim sqlResources As String

Dim sqlFirst As String

Dim sqlSecond As String



Dim app As New Excel.Application, excelws

Dim Agent As String



sqlResources = "Select * from Resources;"

Set rsResources = CurrentDb.OpenRecordset(sqlResources)



If rsResources.EOF And rsResources.BOF Then

    Beep

    MsgBox "No records!"

    rsResources.Close

    GoTo end_it

End If



Set app = CreateObject("Excel.application")



Set excelws = app.Application

With excelws

.Visible = True



Do While Not rsResources.EOF



Agent = rsResources!FullName



app.Workbooks.Open "Z:\Productivity\Blank.xls", , True



'*********** First Query ***************



sqlFirst = "SELECT Main.FPO, Main.Rec_Date, AI_Queue.[Task Name], Count(Main.MainId) AS CountOfMainId" & vbCrLf & _

"FROM Production_Selection, Main INNER JOIN AI_Queue ON Main.[A&I Queue] = AI_Queue.[A&I Queue]" & vbCrLf & _

"WHERE (((Weekday([Main].[Rec_Date]))<>7 And (Weekday([Main].[Rec_Date]))<>1) AND ((Main.Rec_Date) Between [StartDate] And [EndDate]) AND ((Main.FPO)='" & Agent & "'))" & vbCrLf & _

"GROUP BY Main.FPO, Main.Rec_Date, AI_Queue.[Task Name]"



Set rsFirst = CurrentDb.OpenRecordset(sqlFirst, dbOpenDynaset)



'************* Second Query ***************



sqlSecond = "TRANSFORM Sum(rsFirst!CountOfMainId) AS SumOfCountOfMainId" & vbCrLf & _

"SELECT rsFirst!FPO, rsFirst![Task Name]" & vbCrLf & _

"FROM Days_Month LEFT JOIN rsFirst ON Days_Month.Date = rsFirst!Rec_Date" & vbCrLf & _

"GROUP BY rsFirst!FPO, rsFirst![Task Name]" & vbCrLf & _

"PIVOT Days_Month.Date"



Set rsSecond = CurrentDb.OpenRecordset(sqlSecond, dbOpenDynaset)



.Range("A2").copyfromrecordset rsSecond



excelws.activeworkbook.SaveAs ("Z:\Productivity\BlankReport_" & rsResources!FullName & ".xls")

excelws.activeworkbook.Close



rsResources.MoveNext

Loop

End With

Set excelws = Nothing

Set app = Nothing



rsResources.Close



end_it:

Set rsResources = Nothing

End Sub