Hi, all! I have a vba sub that is attached to the ok button on a Dialog box. The info in the dialog box is used to filter another form when it is opened. Now, I want to print a report from the newly opened form based on the filter information in the dialog box. I need to keep the form open to use the information. However, the dialog box closes automatically and doesn't stay open. How do I keep it open so that I can use the info in my print report sub? Thanks for your help!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations sizbut on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Form Closes Automatically - Not 2
- Thread starter 0212
- Start date
ZmrAbdulla
Technical User
Please post your code behind the OK button.
________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.
________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.
- Thread starter
- #4
Hi, all! I figured out how to transfer filter information to the report for which I want to print through a command button. However, there is an unbound text boxe to which I assign information to the new form. I tried to assign the same information to the report via the form textboxes (the report and form are identical). The info does not transfer. I even tried to use a Public Variable, the information was not transferred. I have included applicable code behind the "ok" button from the DoCmd in the dialog box and the code behind the command button on the form. Thanks for any help!
Dialog Box (applicable code only)
DoCmd.OpenForm " FrmTrackRptDatesODR ", acNormal, acEdit, strWhere
Forms!FrmTrackrptDatesODR.Text110.Value = "Risk Assessment Report"
Command Button in "FrmTrackRptDatesODR"
Me!Text110 = Reports!RptFrmTrackRptDatesODR.Text110.Value
Dialog Box (applicable code only)
DoCmd.OpenForm " FrmTrackRptDatesODR ", acNormal, acEdit, strWhere
Forms!FrmTrackrptDatesODR.Text110.Value = "Risk Assessment Report"
Command Button in "FrmTrackRptDatesODR"
Me!Text110 = Reports!RptFrmTrackRptDatesODR.Text110.Value
-
1
- #5
Each of my applications contains a table called "single1". It always has exactly one record. Each field in single1 is analogous to a global variable. This technique eliminates the problems you describe. It also lets you do joins involving your global variables.
- Thread starter
- #6
Let's assume you have a table called CARS with the primary key carID. You also have single1, which contains the field selectedCARID. You want to create two forms, form1 and form2. On form1, the user selects a CARID. On form2, the user views details for that car.
Q. On form1, how does the user select a carID?
A. Make a combobox that displays each distinct value in cars.carID.
Q. When the user leaves form1, how do I store the carID in single1?
A. Put code in the on-click event of the submit button. This code stores the value, then moves the user to the next form. Here is the syntax:
Private Sub Command8_Click()
Dim x As String
Combo11.SetFocus
x = Combo11.Text
Command8.SetFocus
DoCmd.RunSQL "update single1 set selectedcarid = " & x
DoCmd.Close acForm, "form1", acSaveYes
DoCmd.OpenForm "form2"
End Sub
If the ID field was text instead of a number, you would need a slightly different RunSQL command. something like... "update single1 set selectedcarid = " & chr(34) & x & chr(34)
Q. When the user views the selected CAR, how do I use single1.carID?
A. Obviously the form's data source would be a query. Here is an example of the sql:
SELECT carID, carModel, carColor
FROM cars
WHERE (carID=DLookUp("selectedCarID","single1"));
Q. How would I use a value from single1 in VBA?
A. dlookup works in vba. Declare a local variable in your function or sub. Then set its value using dlookup. If you want to set the value of a field in single1, use docmd.runsql
Q. What could go wrong if I use this approach?
A.Values in tables are saved in cleartext. So do not store connection strings in single1. They will be very vulnerable if a hostile user gets the database.
Access does not work well in a multi-user environment. However, if you are forced to accomodate multiple users, then you will need to give each user their own client db with a copy of single1. Otherwise, they will overwrite each other. This problem also happens with traditional global variables.
Q. On form1, how does the user select a carID?
A. Make a combobox that displays each distinct value in cars.carID.
Q. When the user leaves form1, how do I store the carID in single1?
A. Put code in the on-click event of the submit button. This code stores the value, then moves the user to the next form. Here is the syntax:
Private Sub Command8_Click()
Dim x As String
Combo11.SetFocus
x = Combo11.Text
Command8.SetFocus
DoCmd.RunSQL "update single1 set selectedcarid = " & x
DoCmd.Close acForm, "form1", acSaveYes
DoCmd.OpenForm "form2"
End Sub
If the ID field was text instead of a number, you would need a slightly different RunSQL command. something like... "update single1 set selectedcarid = " & chr(34) & x & chr(34)
Q. When the user views the selected CAR, how do I use single1.carID?
A. Obviously the form's data source would be a query. Here is an example of the sql:
SELECT carID, carModel, carColor
FROM cars
WHERE (carID=DLookUp("selectedCarID","single1"));
Q. How would I use a value from single1 in VBA?
A. dlookup works in vba. Declare a local variable in your function or sub. Then set its value using dlookup. If you want to set the value of a field in single1, use docmd.runsql
Q. What could go wrong if I use this approach?
A.Values in tables are saved in cleartext. So do not store connection strings in single1. They will be very vulnerable if a hostile user gets the database.
Access does not work well in a multi-user environment. However, if you are forced to accomodate multiple users, then you will need to give each user their own client db with a copy of single1. Otherwise, they will overwrite each other. This problem also happens with traditional global variables.
- Thread starter
- #8
Thank you, ohiosteve!! I really appreciate your insight. I will try this method. However, I am concerned by your last paragraph. My database will be deployed in a multi-user environment. I was planning to put both the front end and back end up on a Citrix Server to help reduce maintenance time and to speed up response since the support would be a one man show. There may me up to 40 users. I will set up user-level security for each user. Each user will have access only to their data. Managers would have access to all. Any ideas? Thanks again!
Jim
Jim
Well, we are getting off of the original topic but I will give you my opinion.
In my personal experience, Access sucks as a backend for a multi-user db. I have experienced these specific problems with it:
1. It seems to have almost no ability to manage multiple threads.
2. It cannot run as a service and that complicates nighttime maintenance. Oh, and it has nothing like jobs or triggers.
3. The user-level security wizard is hard to understand. The .mdw files it creates are laughably easy to subvert. They are also terribly difficult to manage...you can easily end up with multiple .mdw files on one box.
Most access databases are designed for just one user at a time, so you don't have to worry about these problems. If you want to use Access on a multi-user application, buy SQL Server and use it as a backend....only use access on the frontend.
In my personal experience, Access sucks as a backend for a multi-user db. I have experienced these specific problems with it:
1. It seems to have almost no ability to manage multiple threads.
2. It cannot run as a service and that complicates nighttime maintenance. Oh, and it has nothing like jobs or triggers.
3. The user-level security wizard is hard to understand. The .mdw files it creates are laughably easy to subvert. They are also terribly difficult to manage...you can easily end up with multiple .mdw files on one box.
Most access databases are designed for just one user at a time, so you don't have to worry about these problems. If you want to use Access on a multi-user application, buy SQL Server and use it as a backend....only use access on the frontend.
- Thread starter
- #10
Ohiosteve, thank you for your insight again! I will see if we can upgrade after initial implementation - I have some screaming potential users. Hopefully, since all will be maintaining and reporting on their own data (mostly), it will be doable until we can upgrade it. Thanks!
Jim.
Jim.
-
1
- #11
hovercraft
Technical User
Greetings,
Hey Steve just a suggestion, but rather than using a table to hold the value of the global variable why not just return the variable in a function. This way you can use the function in queries and the like and don't have to increase the size of the database and it also cuts down on the whole "compact and repair" thing.
-Hovercraft
Hey Steve just a suggestion, but rather than using a table to hold the value of the global variable why not just return the variable in a function. This way you can use the function in queries and the like and don't have to increase the size of the database and it also cuts down on the whole "compact and repair" thing.
-Hovercraft
- Thread starter
- #12
- Status
Similar threads
- Locked
- Question
- Locked
- Helpful tip
- Locked
- Question