Help with recordset!!

I am having a problem with a project that I am working on for my Visual Basic 2 class, and my teacher doesn't seem to be able to help me. We are supposed to create an MDI project. When the project starts up just the MDI Parent for m loads. The user then has to click on File then Open Customer. When this happens, an input box comes up for the user to enter a customer number into. After they enter the number the program goes out, creates an instance of the form, pulls the necessary records from a database table and assigns them to the appropriate text boxes on the Child form, then shows the child form. The problem is, is that if the user opens up another customer window, say customer #2,and the first time they opened up customer #1, the correct records load. But if the user switches back to the #1 form, because it is still open, and moves to the next record, the #2 records show up. I asked my teacher, and he said that I should get the parent form to pull the information and assign the recordset to the child form, but I have no idea how to do this. We are not supposed to use the Data control either. Here is what my code looks like:



Public Sub OpenCustomerRecords()
Dim intCounter As Integer

intCustomerNumber = InputBox("Enter customer number")

Set gdbMentor = _
OpenDatabase("C:\1076-5\Chapter.11\Exercise\Mentor.mdb")

If intCounter < 1 Then
intCounter = 1
ReDim gchildEx3Array(intCounter)
gchildEx3Array(intCounter).Tag = intCounter
gchildEx3Array(intCounter).Caption = _
"Mentor Customer Tracking - Customer Number " & intCustomerNumber gchildEx3Array(intCounter).Show

Else
intCounter = UBound(gchildEx3Array)
ReDim gchildEx3Array(intCounter)
gchildEx3Array(intCounter).Tag = intCounter
gchildEx3Array(intCounter).Caption = _
"Mentor Customer Tracking - Customer Number " & CustomerNumber
gchildEx3Array(intCounter).Show
End If


End Sub

The following is the code in the child form:



Private Sub Form_Load() Dim pstrFROM As String
Dim pstrWHERE As String
Dim psqlSELECT As String

pstrFROM = " FROM tblCustomer"
pstrWHERE = " WHERE fldCustomerID = " & "'" & intCustomerNumber & "'"

psqlSELECT = "SELECT *" & pstrFROM & pstrWHERE
Set gdbMentorRecords = _
Module1.gdbMentor.OpenRecordset(psqlSELECT)

Call LoadCurrentRecords

End Sub

Public Sub LoadCurrentRecords()
On Error GoTo LoadCurrentRecords_error:

txtCustomerID = gdbMentorRecor txtPerson = gdbMentorRecords![fldPersonName] txtAddress = gdbMentorRecords![fldAddress]
txtCity = gdbMentorRecords![fldCity]
txtState = gdbMentorRecords![fldState]
txtZip = gdbMentorRecords![fldZipcode]

Exit Sub

LoadCurrentRecords_error:

End Sub

Any help would be great!

If I understand you, you said the user can move to the next record when he / she is in the child form. If this is so, then all you need to do is just not letting the user to navigate the records.

I hope this will help you!

Hi ...

I think that the problem is taht you use only one Rs for the proyects.

You must create a new instance of ADODB.Recordset for each new form that you open, after the inputbox appears. When you close then form, you must destroy it.

(Sorry i don´t speak English very much)

good look. :)

The problem states that the user should be able to have multiple instances of the child for open. If the user decides that they want to view all the records with Customer #1 (because there are multiple records) and all the records with Customer #2 at the same time they should be able too. The problem lies in the fact that I can't get the recordset assigned specifically to each child form that is opening. I was hoping that I could you a redim statment like you do to create new instances of the form, but you can't. Thanks for trying to help though, I appreciate it.

Sorry to say this, but I tried to recreate what you wanted to do with the code you gave, and it was a mess. You are asking someone to help answer why something is doing something it shouldn't but, I can't recreate the problem without more code.

Send the project and its code and I'll be able to answer your question.

[email protected]

I can write something that does exactly what you want, and I did it in about 10 minutes. That might get you an "A" out of the class but it would help you to solve your problems, and code by experience (or assistance). That's what has to happen to become an excellent programmer.

Hope I can help, send the code and I can better answer your questions.

Thanks - Gregg

Well, Don't confuse yourself, clear your mind, and
concentrate, i think better create a recordset for the
users requirement, for example as you mentioned if the user
types a customer#1 then create a separate recordset
like

<<< GENERAL >>>>
dim data as database
dim rec as recordset

private sub form_activate()
set data=opendatabase("mdb name with full path")
end sub

private sub text1_lostfocus()
set rec=data.openrecordset("select * from tablename where customer id= " & text1.text
end sub

like this if you open a form2 create a separate recordset
in form2.

i think this will work, though i am also a learner,
reply soon, how this idea works?

bye,
from [email protected] (anand)

Is it true that the parent form should do all the query and information manipulation, and child forms are only for displaying the records? If this is true that the problem can be solved simply, just do this:

1. In the child's form module declare a Public property as a Recordset (for example call it MyRecordset).

2. Before actually loading a child's form, the parent form does all the searching, and whatever else is needed. When this is done, the parent's Recordset object now contains the records for example on Customer #1.

3. Now you load a child form, and assign the parents's Recordset object to the child's recordset object named MyRecordset:

Load frmChild
Set frmChild.MyRecordset = <DatabaseObjectName>.Recordset

Now every child form will have its own Recordset property, so they won't interfere with each other.