Listbox, multiple forms . (DAO). help!

[Maypen]

Hi folks. I am new to Vb but with your help i have been learning a lot. I have another problem. I have a project (DAO)with multiple forms. The first form has a list box (which is populated on the form load procedure from MS Access database) from which you choose a country. Their are 5 countries. The selected country brings up a second form for the relevant country (there is a different form for each country). On the country form (lets call it France) are labels which on loading should be populated with the info from the Access database, eg. capital,population. I know how to populate the combo box and then get it to display in the label on one form but i dont know how to do that on two or more form. Your help would be much appreciated.Please explain in simple terms and with syntax if possible. Which form do i put the code and under which event? Thanks.

Private Sub Form_Load()

Set db = DBEngine.OpenDatabase("C:\My Documents\Main Travel .mdb&quot
Set rs = db.OpenRecordset("select Country from Countries&quot

List1.Clear

If rs.RecordCount <> 0 Then
Do While Not rs.EOF
List1.AddItem rs(&quot;Country&quot
rs.MoveNext
Loop
End If
db.Close
'rs.Close
Set db = Nothing
Set rs = Nothing
End Sub

 

[tty0]

Hi, guess who!

lets say you have your form loaded and a button on the form so the user can select the country and then click the button to open a form this behind the click event of the button would open a new form dependant on the country and populate the boxes.
************************************************

Set db = DBEngine.OpenDatabase(&quot;C:\test.mdb&quot
Set rs = db.OpenRecordset(&quot;select * from Countries WHERE Country ='&quot; & List1.Text & &quot;'&quot

select case List1.text
case &quot;Britain&quot;
with UKForm
.Show
.Label1.Caption = rs(&quot;Name&quot
.Label2.Caption = rs(&quot;Code&quot
.Caption = rs(&quot;Country&quot
end with
case &quot;America&quot;
with USForm
.Show
.Label1.Caption = rs(&quot;Name&quot
.Label2.Caption = rs(&quot;Code&quot
.Caption = rs(&quot;Country&quot
end with
case &quot;Germany&quot;
with GEForm
.Show
.Label1.Caption = rs(&quot;Name&quot
.Label2.Caption = rs(&quot;Code&quot
.Caption = rs(&quot;Country&quot
end with
end select
db.Close
'rs.Close
Set db = Nothing
Set rs = Nothing
***********************************************
a select statement takes the value and compares it to the case as it goes down the list once it reaches the correct case it executes the code in the statement then quits out.

Hope that helps. 'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[CajunCenturion]

There are lots of ways to address this issue, with tty0 presenting one option. But there may be a couple of other options.

Some questions:

How is the Country Name related to the associated form name? Is it simply hard-coded, (generally not the best approach)?

Is each country's form different, or is the same data being shown on each form? (Values may be different, but it is all the same items?) If it is all the same, then I would ask why use separate forms?
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[Maypen]

ttyo thank you for you quick response. I am at work right now and can't try your code, but just looking at it, it seems the solution i am after. I'll let you know.

Cajun Centurian: Thank you for your quick response also. I didn't understand your first question. For your second question the answer in no. All the data is the same but images on the forms are different. Thanks

 

[CajunCenturion]

The thrust of the first question is how will know, based on the value of the item selected in the list box, what is the correct name of the form to load. tty0's solutions represents a hard-coded example where the name of the form, and the name of the value from the list box is hardcoded. Given that example, suppost that the name of &quot;America&quot; is changed to &quot;USA&quot; in the database. Then that section of the case statement Case &quot;America&quot; will never be executed since &quot;America&quot; no longer is in the list box.

If all of the data is the same, then why create a separate form for each country. Create one form that contains all of the required data items. You could create one form, then you could create one form, with picture boxes, then upon selection of the listbox item, load them form, then load the data from the recordset, and load the picture boxes with the pertitnent images. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tty0]

Hi again,

yes centurion is right there are many ways you can sort out your forms and hard coding is not the best way to go, the way I gave you is one of them but I have to agree with centurion that hard coding the case statement would mean a lot of code and a hell of a lot of forms. However I didnt know the reason you had for having so many forms, hence why i answered that way.
So am I right in saying that you have all these different forms because there is a different image for all of them?
say you have one form with a few labels on that you will populate in the same way and a picture box which will change depending on the language, something similar to what ceenturion has put forward.
make a folder in the same directory as the program files called images and label each image the same as the countrry name that is in the database. Then when you populate the labels you can populate the picture box with something like the following:

picturebox1.picture = loadpicture(app.path & &quot;\images\&quot; & rs(&quot;Country&quot & &quot;.bmp&quot

which for instance will in teh case of the country being Britain:

c:\installationdirectory\images\Britain.bmp

as you say you are new to this i'll quickly explain that bit, app.path returns the absolute path to the directory of the installation folder.


hope that helps
cheers
'mi casa es su casa'
]-=tty0=-[
ICQ:82621399

 

[Maypen]

ttyo and CajunCenturian. Thank you both. Sorry it's taken awhile to reply as i caught a bug and i am just about recovering. Anyway, ttyo: Your code worked beautifully except that the France code is showing the Spain data. The others are fine.Iwill try to find the bug.
You guys are right, i should have used only one form but being new to this it presented all kind of problems. I tried it out and had problems loading pictures from the database. Someone suggested i tried using an Ole container but although the pictures are definately in the database they will not load.Any ideas if not i will try your suggestion ttyo

Private Sub List1_Click()

Set db = DBEngine.OpenDatabase(&quot;C:\My Documents\Main Travel .mdb&quot
Set rs = db.OpenRecordset(&quot;select * from Countries WHERE Country ='&quot; & List1.Text & &quot;'&quot

Select Case List1.Text
Case &quot;France&quot;
With frmFrance
.Show
.lblFrancePopulation.Caption = rs(&quot;Population&quot
.lblFranceCapital.Caption = rs(&quot;Capital&quot
.lblFranceCurrency.Caption = rs(&quot;Currency&quot
'.oleFranceFlag = rs(&quot;Flag&quot
'.oleFranceCostumes = rs(&quot;Costumes&quot
End With
Case &quot;Germany&quot;
With frmGerman
.Show
.lblGermanyPopulation.Caption = rs(&quot;Population&quot
.lblGermanyCapital.Caption = rs(&quot;Capital&quot
.lblGermanyCurrency.Caption = rs(&quot;Currency&quot
'.oleGermanyFlag = rs(&quot;Flag&quot
'.oleGermanyCostumes = rs(&quot;Costumes&quot
End With
Case &quot;Italy&quot;
With frmItaly
.Show
.lblItalyPopulation.Caption = rs(&quot;Population&quot
.lblItalyCapital.Caption = rs(&quot;Capital&quot
.lblItalyCurrency.Caption = rs(&quot;Currency&quot
'.oleItalyFlag = rs(&quot;Flag&quot
'.oleItalyCostumes = rs(&quot;Costumes&quot
End With

Case &quot;Spain&quot;
With frmSpain
.Show
.lblSpainPopulation.Caption = rs(&quot;Population&quot
.lblSpainCapital.Caption = rs(&quot;Capital&quot
.lblSpainCurrency.Caption = rs(&quot;Currency&quot
'.oleSpainFlag = rs(&quot;Flag&quot
'.oleSpainCostumes = rs(&quot;Costumes&quot
End With

End Select
db.Close
'rs.Close
Set db = Nothing
Set rs = Nothing

frmWelcome.Hide
End Sub

 

[tty0]

Yup yup yup,
looks ok to me apart from the bit you mention, to see the picture you need to embed it so something like:

.oleSpainFlag.createembed (rs(&quot;Flag&quot)
.oleSpainCostumes.createembed (rs(&quot;Costumes&quot)

would work for you.

If you still cant see them test out the code by just using a string i.e.
.oleSpainFlag.createembed (&quot;C:\picturefilename.bmp&quot

should just show a picture you pass it.

'mi casa es su casa'
]-=tty0=-[
ICQ:82621399