really stuck

[pdbaker]

i have been trying to for the last several weeks create a database which holds information about companys e.g addresses etc on it. It also has a subform on it which runs a query and shows me the courses they run with a button beside it. This subform propertys is set to continues and so there is a button that appears beside each course. How can i get it so when i click on the botton beside that particualr course it will load up another form and display some more information holding the times and dates of this course.

 

[RuralGuy]

What data is available in the controls of the Continuous form and what code do you have behind the Command Button currently? What form are you attempting to open and what key piece of data will put you on the correct record?

 

[pdbaker]

the command button just runs a macro which closes down the current form and then opens up the other one. on this second form im wanting to pull up data about dates and times from that course i have select on the first form.

 

[Banana]

If you close the original form before you open the second form, you may have nothing to pass to second form. You should open second form *first* before you close the original form.

 

[RuralGuy]

If you are using ac2k+ then use code and either set the Where condition or pass the course info in the OpenArgs argument.

 

[KeithG]

Couldn't you use the DblClick event of your subform? You could have the user double click the record in the subform and have a form pop with the class information.

 

[pdbaker]

thanks

hi Keith G didnt think of it like that i may try this tomrrow and see what happends.

 

[Steve R.]

I hope this code helps. It runs (drop downlist) by clicking on a project type (equivilent to a company name) and will pull up all records of that project type (Subsitute a company name)
-----------------------------------------------------------------------

Private Sub agency_Click()

Dim stDocName As String
Dim stLinkCriteria As String
Dim MSG1 As String
Dim MSG2 As String
Dim numrecordcount As Integer

stDocName = "edit4frm"
MSG1 = "There are no results to display."
MSG2 = "NO Results"
numrecordcount = DCount("agency", "projectnumqry", "[projectnumqry]![agency]=forms![typefrm]![agency]")
stLinkCriteria = "[projectnumqry]![agency]=" & Me![agency]

Debug.Print numrecordcount
If numrecordcount > 0 Then
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit
Forms![edit4frm].OrderBy = "[projectnum]"
Forms![edit4frm].AllowAdditions = False
Forms![edit4frm].[Text456] = numrecordcount
Else
MsgBox MSG1, vbOKOnly, MSG2
End If

End Sub

 

[pdbaker]

Code

I have tried amending the code to work in the drop down list box. I have a table called contacts and a query called Training Course Query are you able to amend the code and paste it back to where these go so i can compare it to mine to see if im doing it right .

cheers

 

[wardster]

I have a similar scenario but use a list box and add the following to the "on_double_click" event:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "form_you_want_to_open"

stLinkCriteria = "[ID]=" & Me![List0]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I would have thought the same would apply to your scenario where by you add to the on click event but the [List0] would be the primarykey of your record. which would need to be on the form but maybe hidden?

ian

 

[Steve R.]

I have tried amending the code to work in the drop down list box. I have a table called contacts and a query called Training Course Query are you able to amend the code and paste it back to where these go so i can compare it to mine to see if im doing it right .

I do not know how your database is structured, so some of my advise may prove to be irrelevent. I would recommend the following, a COMPANYTBL table where each company is identified through the autonumber feature. This table would also contain the contact information for that company. Another table would be the COURSETBL which is linked to the COMPANYTBL by the company idnum. You would then create ONE query using just the fields you need for your form to display. You could call this the RESULTSQRY.

For the dropdown list, the recordsource property is: SELECT [projectnumqry].[projectnum], [projectnumqry].[projectname] FROM projectnumqry WHERE [status]<10; . This can be modified to: SELECT [courses] FROM resultsqry WHERE [companyidnum]= Forms![formname]![companyidnum]. In theory this should list (in the dropdown list) all the courses that a particular company is offering. Please note that the form (formname) itself should be designed to scroll through the company list so you will need a field titled COMPANYIDNUM.

To open the form on a particular course, the code stLinkCriteria = "[projectnumqry]![agency]=" & Me![agency] can be modified to:stLinkCriteria = "[resultsqry]![courseidnum]=" & Forms![formname]![courseidnum]. Note that FORMNAME refers to the underlying form. You will actually be calling up a new form, which you could call FORM2.

 

[pdbaker]

thank you

thank you for your post i will try and have another look at this at see how i get on with the database. this may prove to be very useful and i will let you know how i get on.

phill