Changing RecordSourcw

Hi all,

I'm using Visual Basic 4. My project contains 2 forms. Form 1 has a button and a check box. Form 2 has few text boxes and a data control. My access file contain 2 queries. I have made the necessary links between the text boxes, my database and the data control.

Note: I have set the RecordSource of my datacontrol in from2 to query2. And  my check box in form1 is checked by default.


I am trying to write a code that will show form2 and display the information in query1 when I click the Button on form1 without ticking the check box, however if the check box was ticked then the test boxes in  form2 should display information from query2. This is the code I have  written in the button command1_click:

If Check1.Value = 1 Then

Form2.Show
form1.Hide

Form2.Data1.RecordSource = query2

Else

Form2.Show
form1.Hide

Form1.Data1.RecordSource = query1

End If

But it doesn't work, I keep getting information from query2 displayed wethear the check box was ticked or not despite the the 'Else' condition I  have set in my code. Can anyone tell me why won't it work? Many Thanx!

Your help is appreciated
Belle

BelleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dirtdartCommented:
It didn't work because you didn't change the data field of your text boxes, so they didn't know which fields to get their data from.  However,even if you did, it probably still wouldn't work, because text boxes don't bind to record sources very well at runtime.  An easier solution would be to use dao.  Start out by adding a module to your project.  In that module, Add the following code:

Dim db As Database
Dim rs As Recordset

In the Form_Load event of your first form, add this line:

Set db = Workspaces(0).OpenDatabase(DatabaseName)

Where database name is the full path and name of your database.

Then in place of the code you have shown above, put this

If Check1.Value = 1 Then

Form2.Show
Form1.Hide
Set rs = db.OpenRecordset(query1)
Form2.Text1.Text = rs(Field1)
Form2.Text2.Text = rs(Field2)

else

Form2.Show
Form1.Hide
Set rs = db.OpenRecordset(query2)
Form2.Text1.Text = rs(Field1)
Form2.Text2.Text = rs(Field2)

End If

This should do exactly what you are wanting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BelleAuthor Commented:
Hi again,

I did what you have asked me to do but I keep getting the run time error 424 at the statement:
Set rs = db.OpenRecordset(query1)

One more thing, where you've asked me to fill in the fields in the statment:
Form2.Text1.Text = rs(Field1)
Am I going to have to add all the fields in my second form in the code? because I have around 20 fields displayed on my form, it's gonna make it one long code statement.

Thank you for your help,
Belle
0
dirtdartCommented:
We might have had a misunderstanding on the query1.  That was just meant to be a variable representing the name of your query.  If query1 is the actual name of your query, then it has to be enclosed in quotations.  And yes, you have to fill in each field individually, and it does make for a somewhat long subroutine, but in the long run turns out much better.   I know because I've had to do it for 60 fields a couple of times.  Hope it works out.
0
BelleAuthor Commented:
Hello again,

My fiance found a different solution which you might find useful in the future to use instead of writing endless code statments.

Create a module and type in it:
Public Varname

Then in the button command1_click type the following code:

if check1.value = 1 then
      varname = "query1"
else
      varname = "query2"  
end if
load form2
form2.show

Then in form2, under the form load type:
data1.recordsource = varname

If you have a "Go Back" button on form2 then remember to unload form2.

I've tried this method and it works pretty good plus it's much shorter, I thought I'd let you know to save you time in the future.

Belle
0
dirtdartCommented:
Thank you for sharing that with me.  However, in my experience, the shortcomings of the data control far outweight the benefits of not writing a little code.  If you ever get into generating serious reports (without Crystal) then you'll see what REALLY long code is! :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.