If I created a Recordset in VBA, how do I make certain fields show up
in a form? 5 1955
"Rachel Garrett" <rm*******@gmail.comwrote in message
news:08**********************************@t54g2000 hsg.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:08**********************************@t54g2000 hsg.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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Kenshin |
last post by:
Hey!
I have another script where i pull all the information from the database and I want to send it to the person. What they do is they enter in their email, and if the email matches, than it will...
|
by: Thelma Lubkin |
last post by:
I have a form/subform with the common one-to-many relationship.
The form allows user to display records and move to other records
via the selector, to add,delete, and edit them, with the related...
|
by: David Lozzi |
last post by:
It use to be so easy in ASP, but now in ASP.NET i'm lost and confused.
I'm trying to connect to a SQL database, open a table via a SQL string. Then
take the returning recordset and do what I want...
|
by: Jeremy Dillinger |
last post by:
I have a program setup to pull data from a database. My database table has
things such as (category, Item, price, etc.) In my program I want to have
multiple list boxes that will have a pull down...
|
by: whitc26 |
last post by:
Let me preface: I'm a novice, and have no programming experience.
I have created an access database and have a few tables in it. I have
created a form called "clients" This form opens up and...
|
by: Kosmos |
last post by:
Hey guys...as a relatively new programmer, I try to give back where I can. Below is the code for a useful program I believe many of you could use...just don't use the same exact code because...well I...
|
by: SkipNRun |
last post by:
I am a novice when comes to JavaScript, AJAX. I am working on a form, which will allow users to update their contact information. In order to make the form flexible, I need to use pull down list.
...
|
by: giandeo |
last post by:
Hello Experts.
Is it possible to retrieve the value from a populated pull down menu from a database and then use that value to access the same database to get the related fields.
Example:
...
|
by: giandeo |
last post by:
Hello Experts.
Could you find a solution for this problem please!
I have the following tables in Access Database
Table Name: origin
Fields Names: country, countrycode
Table Name: make...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |