Solved

Changing RecordSourcw

Posted on 1997-10-31
5
122 Views
Last Modified: 2008-03-06
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

0
Comment
Question by:Belle
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
dirtdart earned 100 total points
ID: 1439446
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
 

Author Comment

by:Belle
ID: 1439447
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
 
LVL 5

Expert Comment

by:dirtdart
ID: 1439448
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
 

Author Comment

by:Belle
ID: 1439449
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
 
LVL 5

Expert Comment

by:dirtdart
ID: 1439450
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now