the only line that is actually highlighted when i try to debug is:
Set rst = CurrentDb.OpenRecordset("Trace data")
right now i can't pinpoint a problem because the logic makes sense. also i
am confident of that the query is running without the form perfectly it's
just that the textboxes are not being populated.
if you can think of anything else at some time let me know.
thank you.
:
Then we need to go into debug mode and find out what is happening.
Open the VBA editor to where the code is. Put your cursor on this line:
If rst.RecordCount = 0 Then
Press F9
Open your form in form view, enter something in Text2. When you complete
the entry, go back to the VBA editor. You can now step through the code line
by line by pressing F8. Look at the values to see what you are getting. The
first thing is to see if you got anything returned in the recordset.
:
Klatuu, i typed the very first code that you gave me all over instead of copy
and pasting. The error message is no longer there but when i hit enter, the
cursor just move to the text box where the result of Level 1 should be and
they are all empty.
:
please don't give up on me Klatuu, i am desperate. i am here playing with it
as well trying to get it to work.
with your new code a compile error was found: user defined type not defined.
The following line was higlighted...
dim qdf As QueryDef
I really appreciate your help.
:
Okay, try it like this:
Private Sub Text2_AfterUpdate()
Dim rst As Recordset
Dim qdf As QueryDef
Set qdf = Currentdb.QueryDefs("Trace data")
qdf.Parameters(0) = Me.Text2
Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly)
If rst.RecordCount = 0 Then
MsgBox "Not Found"
Else
Me.Text5 = rst![Level 1]
Me.Text7 = rst![Level 2]
Me.Text9 = rst![Level 3]
Me.Text11 = rst![Level 4]
Me.Text13 = rst![Level 5]
Me.Text15 = rst![Level 6]
Me.Text17 = rst![Machining Drawing Number]
End If
rst.Close
Set rst = Nothing
End Sub
:
I just tried doing the query and the form all over again. All i have on the
form is a list box, the Text2 box where i will enter the part number, and the
different textboxes that i want to populate with the results from the query.
i right click in Text2, went to bulid event then code and type the exact
thing. when i open the form and enter the part number in Text2 then press
enter on the keyboard, the same exact message appears.
i already had the form reference that you mentioned. Is there anything else
you can think of.
The first line is still higlighted in the code.
thank you.
:
If you have the code in the After Update event, you should not have to press
a run query button. The code will execute when you enter a value in Text2.
The problem is you have to identify the parameter value in the query. You
sould be able to open your query in design view and in the Criteria row for
the field you are matching the value in Text2 to, reference the form and
control:
Forms![MyFormNameHere]![Text2]
:
almost there. this is what i have:
Private Sub Text2_AfterUpdate()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("Trace data")
If rst.RecordCount = 0 Then
MsgBox "Not Found"
Else
Me.Text5 = rst![Level 1]
Me.Text7 = rst![Level 2]
Me.Text9 = rst![Level 3]
Me.Text11 = rst![Level 4]
Me.Text13 = rst![Level 5]
Me.Text15 = rst![Level 6]
Me.Text17 = rst![Machining Drawing Number]
End If
rst.Close
Set rst = Nothing
End Sub
Trace data is the name of my query. when i press the run query button on my
form the following message appear: "run time error '3061': Too few
parameters. Expected 1.
i pressed debug and this line was highlighted:
Set rst = CurrentDb.OpenRecordset("Trace data")
please help, thank you.
:
The code should be in the After Update event of whatever control you are
using to enter the search criteria.
As to establishing a recordset, this line does that:
Set rst = CurrentDb.OpenRecordset("YourQueryNameGoesHere")
CurrentDb should not be changed. It is the name to the Application object
that contains the currrent database.
The only things that need to change in the code are the name of the query
and the name of the controls you want to populate.
:
Once again thank you Klatuu. i apologize for my limited knowledge of access
but i am learning more each day.
i would just like if you could go into a little more detail as to how i can
create a recordset. Also, where exactly am i inserting this code?
In your code, should i replace "CurrentDb" with the name of my database with
Db at the end as you have it?
Finally, with the Run Query command button that i have in my form, if i am
not going to run the query how do i modify the search command to actually
perform the search and have the results displayed on my form the way i would
like it?
thank you very much.
:
Thanks for the good feed back, makes it easy to give an answer.
What you can do is use the query you are using now but rather than just run
the query, create a recordset, and populate your controls from the recordset
fields.
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("YourQueryNameGoesHere")
If rst.RecordCount = 0 Then
MsgBox "Not Found"
Else
Me.SomeTextBox1 = rst![SomeField1]
Me.SomeTextBox2 = rst![SomeField2]
Me.SomeTextBox3 = rst![SomeField3]
End If
rst.Close
set rst = Nothing
Of course, you will have to user your names.
:
o.k Klatuu, thank you for your quick response. For this particular search all
the part numbers are different that i enter for the search criteria, therfore
only one row should be returned under the different headings "Level1",
"Level2" etc.
I need each of these to be displayed in a different textbox in the form.
thank you so much.
:
Do you always get just one row returned, or is it multiple rows? The reason
I am asking, is that if this is returning one row with a number of fields you
want on your form, you need to do it one way. If you are getting multiple
rows, there are two more possibilities.
1. You want one field from each row in a different text box in the form
2. You get multiple rows with multiple columns you need to see, you will
need a sub form.
:
yes i am getting the result of the query popping up in a grid independent of
my form.
what i really need though is the result corresponding to each column in a
textbox on the form as previously mentioned.
thank you.
:
What is displaying is probably not the table itself, but the results of the
query. Is it displaying in a sub form or is it popping up in a grid
independant of your form?
If you can describe in more detail what your are getting in this view, we
can show you how to get it to display on your form and whether you should be
using a sub form for this data.
:
hello, i am working with a form in which i enter a search criteria, hit the
run query button and a table with the corresponding rows is diplayed based on
what i enter.
Is there a way to display the results within the form itself?
What i enter is a Part #, hit search then i have Level1, Level2 ... Level 6
displayed in the search result.
I do not need a table, what i need is a text box showing me each levels
separately on the form.
How can i modify my form to do this? please be as specific as possible...
much thanks.
