If I created a Recordset in VBA, how do I make certain fields show up
in a form?

"Rachel Garrett" <rmgarrett@gmail.comwrote in message
news:0873f435-9055-4d2e-98c4-2997103418b4@t54g2000hsg.googlegroups.com...

If I created a Recordset in VBA, how do I make certain fields show up
in a form?

I'll assume that you have a form with the following unbound controls,
txtData1 & txtData2. I'll also assume that you have created a recordset with
the name Rst and with fields of Data1 & Data2. I recommend that you use the
OnCurrent event of the form to first search the recordset for the desired
record, then:

If Rst.NoMatch = False Then
Me.txtData1 = Rst!Data1
Me.txtData2 = Rst!Data2
Else
Me.txtData1 = Null
Me.txtData2 = Null
Endif

Good Luck,
Fred Zuckerman

Rachel Garrett wrote:

If I created a Recordset in VBA, how do I make certain fields show up
in a form?

That's a very odd question. Can you provide more info?

Here's a method.
strSQL = "select * From Table1"
Me.RecordSource = strSQL

Boogey
http://www.youtube.com/watch?v=TfmdLmCTLaw

Some more information would be helpful. I give one way below but there
only a few situations in which this might be advantageous.

Private Sub Form_Load()
Dim r As ADODB.Recordset
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
.ActiveConnection = CurrentProject.Connection
.Open "SELECT * FROM Orders"
End With
Set Me.Recordset = r
End Sub


On Jun 4, 11:46*am, Rachel Garrett <rmgarr...@gmail.comwrote:

If I created a Recordset in VBA, how do I make certain fields show up
in a form?

Thank you for the responses so far. I tried posting a more detailed
explanation I was doing in the access forms group, but got no
response. I also found other online forums where people had asked very
similar questions -- none of them had responses either. I'd like to
try to explain what I'm trying to do.

I have a MainForm coming from MainTable. The index field is called
[Question Name]. Each question gets assessed over time, and the
assessors enter a score as well as their comments.

I created a button in MainForm that takes users to SubForm. SubForm
allows them to modify the "main" data, but it also has additional
fields where users can enter their name, the date of the assessment,
and the score. Within SubForm, there is a button to save the record.
This button runs some VBA and SQL that makes updates to MainTable and
also creates a new record in a table called Assessments. The index
field is a concatenation of [Question Name] and [Assessment Date].

The problem now is that assessors want to be able to go back and make
minor tweaks to their comments, if they've just entered it. So I need
to provide functionality that will allow users to get to an old record
OR enter a whole new record, depending on whether or not a record of
an assessment exists from within the past 7 days.

I can write a query in SQL that will look for matching records where
the date is within the past week. But once I have that query, how do I
put the information in the SubForm so that the user can edit it again?

Dim AssessmentExists As Boolean
Dim FoundAssessment As String
Dim myFindSQL As String

'Turn Milestone Assessed into string (was number)
Dim strMilAssessed As String
strMilAssessed = Format(Me.Milestone_assessed.Value(), "0")

'Create recordset and ADO connection, to store the SQL results
Dim myRecordSet As New ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection

Dim strDateMinus7 As String
strDateMinus7 = Format((Date - 7), "MM/DD/YYYY")

myFindSQL = "SELECT [Milestone Assessment Results Processed].Index
"
myFindSQL = myFindSQL + " FROM [Milestone Assessment Results
Processed] "
myFindSQL = myFindSQL + " WHERE ((([Milestone Assessment Results
Processed].[Question Name]) = '"
myFindSQL = myFindSQL + Me.Question_Name + "') AND "
myFindSQL = myFindSQL + "(([Milestone Assessment Results
Processed].[Milestone Assessed]) = "
myFindSQL = myFindSQL + strMilAssessed + ") AND "
myFindSQL = myFindSQL + "(([Milestone Assessment Results
Processed].[Assessment Date]) #"
myFindSQL = myFindSQL + strDateMinus7 + "#))"

'Get results
myRecordSet.Open myFindSQL, conn
If myRecordSet Is Nothing Then
AssessmentExists = False
Else: AssessmentExists = True

End If

I tried making me.Recordsource = myFindSQL, but this doesn't work
because the first couple fields in the subform (Question Name,
Milestone Assessed, Assessment Date) are required as data for the
query. It's circular.

One problem is that the recordset doesn't get created until *after*
the user has entered some information in the form for it to search
on.

The other problem is that although I already have written a search
that returns one matching record, I don't know how to make VBA take
the information in that record and put it back in the form. Recordset!
Fieldname doesn't pull it out. I don't know what other syntax I should
be using.

On Jun 4, 11:53*am, "Fred Zuckerman" <Zuckerm...@sbcglobal.netwrote:

"Rachel Garrett" <rmgarr...@gmail.comwrote in message
>
news:0873f435-9055-4d2e-98c4-2997103418b4@t54g2000hsg.googlegroups.com...
>

>
I'll assume that you have a form with the following unbound controls,
txtData1 & txtData2. I'll also assume that you have created a recordset with
the name Rst and with fields of Data1 & Data2. I recommend that you use the
OnCurrent event of the form to first search the recordset for the desired
record, then:
>
If Rst.NoMatch = False Then
* *Me.txtData1 = Rst!Data1
* *Me.txtData2 = Rst!Data2
Else
* *Me.txtData1 = Null
* *Me.txtData2 = Null
Endif
>
Good Luck,
Fred Zuckerman