Solved

Changing RecordSourcw

Posted on 1997-10-31
5
125 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 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