[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

A few questions about MS Access 2003 Forms

This is my first time trying to make a form of this nature.  Here is what is supposed to happen.  We are scanning in our invoices, trying to save a few trees, the invoces are put into a folder call "To Be Filed".  A user starts the form and sees the .pdf file as shown in the attached picture.  Using the form fills out data from the file and hits save.  Upon saving the data is recored into the database and the file is deleted from the "To Be Filed" folder and moved to the "filed" folder.  It does most of what I want it to do.  There are just a few glitches I need worked out.

The function GetFiles() gets all the files located in the "To Be Filed" folder and places them into a table, this table is used in listbox20.

1. Make it so that the user can't mess with anything else.  Is there a way to have the form open outside of Access?  So that the user will only see the form itself?

2. Get rid of the "record: " line at the bottom of the attached picture.

3.  Twice in the code I try to give a listbox focuse with Me.Company.SetFocus, once in the onload area and the other after the user clicks save.  Each time the focus is set to the pdf viewer.  Is there a fix for that?

4. After a save the .pdf view still shows the file that was deleted instead of showing the next file in the listbox.  If the user click on the file in the listbox it works though.  They both use the same code.


Option Compare Database
 
Private Sub Form_Current()
List20.RowSource = List20.RowSource
Company.RowSource = Company.RowSource
[Fiscal Year].RowSource = [Fiscal Year].RowSource
Owner.RowSource = Owner.RowSource
[Department Number 1].RowSource = [Department Number 1].RowSource
[Department Number 2].RowSource = [Department Number 2].RowSource
[Department Number 3].RowSource = [Department Number 3].RowSource
[Department Number 4].RowSource = [Department Number 4].RowSource
[Department Number 5].RowSource = [Department Number 4].RowSource
[Account Number 1].RowSource = [Account Number 1].RowSource
[Account Number 2].RowSource = [Account Number 2].RowSource
[Account Number 3].RowSource = [Account Number 3].RowSource
[Account Number 4].RowSource = [Account Number 4].RowSource
[Account Number 5].RowSource = [Account Number 5].RowSource
List40.Selected(0) = True
Company.Selected(0) = True
[Fiscal Year].Selected(0) = True
Owner.Selected(0) = True
[Department Number 1].Selected(0) = True
[Department Number 2].Selected(0) = True
[Department Number 3].Selected(0) = True
[Department Number 4].Selected(0) = True
[Department Number 5].Selected(0) = True
[Account Number 1].Selected(0) = True
[Account Number 2].Selected(0) = True
[Account Number 3].Selected(0) = True
[Account Number 4].Selected(0) = True
[Account Number 5].Selected(0) = True
End Sub
 
Private Sub Form_Load()
List40.Selected(0) = True
Company.Selected(0) = True
[Fiscal Year].Selected(0) = True
Owner.Selected(0) = True
[Department Number 1].Selected(0) = True
[Department Number 2].Selected(0) = True
[Department Number 3].Selected(0) = True
[Department Number 4].Selected(0) = True
[Department Number 5].Selected(0) = True
[Account Number 1].Selected(0) = True
[Account Number 2].Selected(0) = True
[Account Number 3].Selected(0) = True
[Account Number 4].Selected(0) = True
[Account Number 5].Selected(0) = True
GetFiles ("S:\Invoices\To Be Filed\")
List20.RowSource = List20.RowSource
If List20.ListCount > 0 Then
List20.Selected(0) = True
filename.Value = Replace(List20.Value, "To Be Filed", "filed")
AcroPDF9.LoadFile (List20.Value)
End If
Me.Company.SetFocus
End Sub
 
Private Sub List20_Click()
filename.Value = Replace(List20.Value, "To Be Filed", "filed")
AcroPDF9.LoadFile (List20.Value)
End Sub
 
Private Sub Save_Click()
If Len([Amount 1].Value) > 0 And Len(Datey.Value) > 0 And Len([Invoice Number].Value) > 0 Then
Dim rs As Recordset
Dim strFile As String, strDate As Date, dept As Integer
Set rs = CurrentDb.OpenRecordset("filedata", dbOpenDynaset)
rs.AddNew
rs!filename = filename.Value
rs![Invoice Number] = [Invoice Number].Value
rs!Company = Company.Value
rs!Date = Datey.Value
rs!Owner = Owner.Value
rs![Fiscal Year] = [Fiscal Year].Value
rs.Update
Set rs = Nothing
 
dept = List40.Value
Set rs = CurrentDb.OpenRecordset("filedepartment", dbOpenDynaset)
rs.AddNew
rs!filename = filename.Value
rs!Amount = [Amount 1].Value
rs![Account Number] = [Account Number 1].Value
rs![Department Number] = [Department Number 1].Value
rs.Update
If dept > 1 Then
rs.AddNew
rs!filename = filename.Value
rs!Amount = [Amount 2].Value
rs![Account Number] = [Account Number 2].Value
rs![Department Number] = [Department Number 2].Value
rs.Update
End If
If dept > 2 Then
rs.AddNew
rs!filename = filename.Value
rs!Amount = [Amount 3].Value
rs![Account Number] = [Account Number 3].Value
rs![Department Number] = [Department Number 3].Value
rs.Update
End If
If dept > 3 Then
rs.AddNew
rs!filename = filename.Value
rs!Amount = [Amount 4].Value
rs![Account Number] = [Account Number 4].Value
rs![Department Number] = [Department Number 4].Value
rs.Update
End If
If dept > 4 Then
rs.AddNew
rs!filename = filename.Value
rs!Amount = [Amount 5].Value
rs![Account Number] = [Account Number 5].Value
rs![Department Number] = [Department Number 5].Value
rs.Update
End If
Set rs = Nothing
[Amount 1].Value = ""
[Amount 2].Value = ""
[Amount 3].Value = ""
[Amount 4].Value = ""
[Amount 5].Value = ""
[Invoice Number].Value = ""
[Datey].Value = ""
 
FileCopy List20.Value, filename.Value
Kill (List20.Value)
 
GetFiles ("S:\Invoices\To Be Filed\")
List20.RowSource = List20.RowSource
If List20.ListCount > 0 Then
List20.Selected(0) = True
filename.Value = Replace(List20.Value, "To Be Filed", "filed")
AcroPDF9.LoadFile (List20.Value)
End If
Me.Company.SetFocus
Else
MsgBox ("This can't be saved there is no amount, date or invoice number entered")
End If
End Sub

Open in new window

CMS.jpg
0
Millkind
Asked:
Millkind
  • 2
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<. Make it so that the user can't mess with anything else.  Is there a way to have the form open outside of Access?  So that the user will only see the form itself?>

No. Access forms must be run within the Access environment. You can set various startup options that will disallow users from browsing through the database, however - click Tools - Startup and review the options there.

<. Get rid of the "record: " line at the bottom of the attached picture.>

Set "Navigation Buttons" to No in the Form Properties.

<.  Twice in the code I try to give a listbox focuse with Me.Company.SetFocus, once in the onload area and the other after the user clicks save.  Each time the focus is set to the pdf viewer.  Is there a fix for that?>

I believe this is similar to another question you've posted. The answer is to simply continue to try and setfocus to your listing after several events. Your PDF viewer may have a FinishedLoading event (or something simillar) in which you could try this.

<. After a save the .pdf view still shows the file that was deleted instead of showing the next file in the listbox.  If the user click on the file in the listbox it works though.  They both use the same code.>

This is likely a timing issue ... you might need to use DoEvents to allow the machine to "catch up", or you may need some looping code to "idle" the machine while files are renamed/moved.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Without going through your code (looks like Scott is on it) here are some tips for you...

(1)  Lose the spaces in your control names, which forces you to use square brackets [ ] to surround them in VBA code., which is one thing to forget and cause an error. For example, [Department Number 1] should be cboDepartmentNumber1 (cbo = assuming it's a combo box)

(2)  I will wager you that six months from now you'll have no idea what [List 20] is without having to look it up.
I highly recommend renaming these controls to something more meaningful, such as lstCustomers, lstOrders, lstDonutFlavorGroup, etc.

(3)  You can loop through controls as long as the naming convension is the same.  
Dim x as Integer
For x =1  to 5
   Me("Amount" & x ).Value  will equal the contents of the Amount1 textbox when x=1, txtTotal2 when x=2, etc.
Next

(4)  Use code comments.   Read (1) 'six months from now...'

'The below lines of code does this damn thing.

'The below lines of code does this other damn thing.

'If the below feed fails, hound {insert name here}.

'You get the idea.

Hope this helps.
Jim
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Excellent advice, jim ...
0
 
MillkindAuthor Commented:
Sorry it took so long for me to post again.  

Jim:
I made all the changes you suggested.  The code looks alot better.  Thanks.

LSM:
The first two answers did what I needed.
The last two however didn't work so well.
The pdf viewer only has seven events (Enter, Exit, Gotfocus, LostFocus, Updated, OnError, OnMessage)
I tried to get a message box to fire on the updated and couldn't get it to fire at any time.
I tried to put in loops and DoEvents but that didn't work either.
0
 
MillkindAuthor Commented:
Nothing here worked so I wrote it in VB.net.  That solved the "after save" problem and the problems associated with using MS Access.  Still won't let me give focus to anything but the pdf viewer.  After looking into it further it seems adobe wants you to buy something from them to be able to control the viewer better.  
Thanks for your help with this.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now