MSAccess Form VBA saving
Good morning,
I have a problem this morning...
I have a form in which I can enter up to 50 numbers and when I click on print, it will print my report as I want to. This work great. The problem is : is there a way that the 50 numbers I have entered will be memorise (saved) when I finished printing, for the next time I will need to print the report. I want to be able to change some number, erase some and keep some......................
Can you Expert help me please. Thanks
form.jpg
code-vba.jpg
I have a problem this morning...
I have a form in which I can enter up to 50 numbers and when I click on print, it will print my report as I want to. This work great. The problem is : is there a way that the 50 numbers I have entered will be memorise (saved) when I finished printing, for the next time I will need to print the report. I want to be able to change some number, erase some and keep some......................
Can you Expert help me please. Thanks
form.jpg
code-vba.jpg
Hello Ste5an,
I have tought a creating a table for that but I don't know how to process from VBA................
I have tought a creating a table for that but I don't know how to process from VBA................
Create a table first. then bind the form to that table. use an continuous form. Then you don't need so much controls.
I would ask why those number were not stored in a table all along.
(in the same table that stores the other form fields)
This would help if:
You need a history of those numbers
You need to see when they were entered? (set a default value of Today's date and time)
So can you could tell us why these numbers are being entered into an "unbound" control?
Just curious...
JeffCoachman
(in the same table that stores the other form fields)
This would help if:
You need a history of those numbers
You need to see when they were entered? (set a default value of Today's date and time)
So can you could tell us why these numbers are being entered into an "unbound" control?
Just curious...
JeffCoachman
Hello Jeffrey, Each month we have a list of job order in a excel form that we need to type in that form for it to give us the report we want. We were not keeping it cause we did'nt need it until now.
Now I have to keep it and be able to delete and modify it on request and print the same report.
I have create a table to put them in but I have to figure out how to link it to my form......
Thanks
Now I have to keep it and be able to delete and modify it on request and print the same report.
I have create a table to put them in but I have to figure out how to link it to my form......
Thanks
Your original design was unnormalized. Because it was flat like a spreadsheet, and had data that repeated in columns rather than rows, you needed to decide at design time the maximum you would allow. Now that you have your data in rows (one row per job), you can technically support an infinite number of jobs and no change would be required if all of a sudden you needed to report on 100 jobs. That is the beauty of normalization. Rows are free, columns are expensive.
You need to import the data from the spreadsheet or link to the spreadsheet. I prefer linking since this isn't something that you actually need to keep. Each time you run the reports, you will be using a new version of the list. The code you have loops through the controls on the form. The new code will open a recordset and loop through the rows in the recordset.
You need to import the data from the spreadsheet or link to the spreadsheet. I prefer linking since this isn't something that you actually need to keep. Each time you run the reports, you will be using a new version of the list. The code you have loops through the controls on the form. The new code will open a recordset and loop through the rows in the recordset.
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim td as DAO.Tabledef
Set db = CurrentDB()
Set td = db.Tabledefs!yourtablname
Set rs = td.OpenRecordset
Do Until rs.EOF = True
.... your print report code
rs.MoveNext
Loop
Hello all,
Thanks for all your answer. I have create my table and yes it is a better idea. Thanks
Mr PatHartman I will try your code for my report.
Thanks again
Thanks for all your answer. I have create my table and yes it is a better idea. Thanks
Mr PatHartman I will try your code for my report.
Thanks again
Hello, It is giving me a non definite something error.........
Is the way I have the date to be a filter will work ?
thanks
Is the way I have the date to be a filter will work ?
thanks
Ok I have figure out the non definite error , the DAO was not include.
Now it seems to print something but very too much.
Private Sub cmdPrint_Click()
Dim sfilter As String
Dim stDocName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.Tabledef
Set db = CurrentDb()
Set td = db.Tabledefs!tbl_NoJobRequ
Set rs = td.OpenRecordset
Do Until rs.EOF = True
'sfilter = "[NO_JOB]=" & ctl & " AND [DATE]>=#" & Format(DateValue(Me.txtDat
sfilter = "[DATE]>=#" & Format(DateValue(Me.txtDat
stDocName = "rpt_Temps Job Particulier sans critere"
DoCmd.OpenReport stDocName, , , sfilter 'not working cause printing a way too much pages.........
rs.MoveNext
Loop
' DoCmd.Close
' DoCmd.OpenForm "menu"
End Sub
Now it seems to print something but very too much.
Private Sub cmdPrint_Click()
Dim sfilter As String
Dim stDocName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.Tabledef
Set db = CurrentDb()
Set td = db.Tabledefs!tbl_NoJobRequ
Set rs = td.OpenRecordset
Do Until rs.EOF = True
'sfilter = "[NO_JOB]=" & ctl & " AND [DATE]>=#" & Format(DateValue(Me.txtDat
sfilter = "[DATE]>=#" & Format(DateValue(Me.txtDat
stDocName = "rpt_Temps Job Particulier sans critere"
DoCmd.OpenReport stDocName, , , sfilter 'not working cause printing a way too much pages.........
rs.MoveNext
Loop
' DoCmd.Close
' DoCmd.OpenForm "menu"
End Sub
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Hello PatHartman,
Thanks you for your help. It is working great now.
Here is my code :
Private Sub cmdPrint_Click()
Dim sfilter As String
Dim stDocName As String
Dim No_Job As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.Tabledef
Set db = CurrentDb()
Set td = db.Tabledefs!tbl_NoJobRequ
Set rs = td.OpenRecordset
Do Until rs.EOF = True
With rs
No_Job = !No_Job
sfilter = "[No_Job]=" & !No_Job & " AND [DATE]>=#" & Format(DateValue(Me.txtDat
stDocName = "rpt_Temps Job Particulier sans critere"
DoCmd.OpenReport stDocName, acViewNormal, , sfilter
End With
rs.MoveNext
Loop
' DoCmd.Close
' DoCmd.OpenForm "menu"
End Sub
Thanks you for your help. It is working great now.
Here is my code :
Private Sub cmdPrint_Click()
Dim sfilter As String
Dim stDocName As String
Dim No_Job As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.Tabledef
Set db = CurrentDb()
Set td = db.Tabledefs!tbl_NoJobRequ
Set rs = td.OpenRecordset
Do Until rs.EOF = True
With rs
No_Job = !No_Job
sfilter = "[No_Job]=" & !No_Job & " AND [DATE]>=#" & Format(DateValue(Me.txtDat
stDocName = "rpt_Temps Job Particulier sans critere"
DoCmd.OpenReport stDocName, acViewNormal, , sfilter
End With
rs.MoveNext
Loop
' DoCmd.Close
' DoCmd.OpenForm "menu"
End Sub
Join our community and discover your potential
Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.
*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.