Question How to improve db and form

[baaz]

Hi'

I am trying to make an access database. I have now made an Form page. But I want to make it this way, so people dont have to go into access. They should be able to get the choice between "search" or "submit" and depending on what they choose, they should get either a submission form or "search" page. Both would have same fields, so a search can be made on any parametres, which can be submitted.

Is it possible to make it like a webpage or something like that, so people dont need to go into access. What I mean is to make a frontend for users, where access database would be running in background.

Here I would also like to know, whether it is "must" to have access in order to automatically update the database, every time a user submit a form. How can I link this to excel, so the list is available as excel sheet also?


So far I have a form and search option, but you have to run it from access. I have removed the menu bars, so users can only type and save. However, this is not the way I want it - I want it either as a pop-up front end window or standalone form without running Access. Further, the search I have integrated is the standard Access search, which can find a word/phrase, but you have to press next and go through them all. I want the search function to enable search on multiple parametres and then a list of the hits are shown, wherefrom you can choose the one you want to see.

Please help me....

 

[namliam]

In the menu
Tools => Startup
Will allow you to pick the form that needs to start when the DB is started.

You must have access or Access-runtime to run an Access DB.

Why want the table in Excel available? It can be done, but should not be needed.

Have a search for "Search form" in the reference part of the forum and you will find some good samples of how to make a nice search form.

Good luck & Welcome to the forum

 

[baaz]

I got Access 2007

Thank you very much for your response and the warm welcome.

I will make a search and get back if there are problems with regard to search page.

The other part I can't figure out. What do you mean by "tools => startup". I have been in the options for the "current database" and chosen the form to be shown. But I don't think that is what you are pointing at...as it open the form in Access and then I have to remove all the menu bars.

Could you please elaborate...

 

[namliam]

Nope, that is exactly what I am pointing at... You will always need access in the background (unfortunatly).

You can hide the menus and the database, but Access will always be there, somewhere.

 

[baaz]

hmm

hmmm...It may be there, but can it be totally hidden. Or should I then make a local webpage as form to update it?

I have searched for "search forms", but the codes seem to be a bit more advanced for a user like me.

I am uploading my db and then you guys can have a look.

Let me know how I can combine it with a search form, the "search form" can be identical to the one I have made, but it should just make the search within those parameters and put forward a list wherefrom it is possible to choose the one you want to have a closer look at.


You are encouraged to tell me other parts to optimize also, e.g. my combobox in the form is not working properly.


To give you the background:

I am trying to make a Lessons Learned db, where project managers after completing a project can submit their experiences, etc. Then before initiating a project, the project manager can make the search and make use of the valueable experience of others.

_______________

Lessons Learned.zip is the 2007 version file
Lessons Learned_2003.zip is the 2002-2003 version file

 

[Pete666]

You can completely hide all access background and only have the form visible by using the module below:

Option Compare Database
Private Declare Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Long
Dim dwReturn As Long

Const SW_HIDE = 0
Const SW_SHOWNORMAL = 1
Const SW_SHOWMINIMIZED = 2
Const SW_SHOWMAXIMIZED = 3

Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, _
ByVal nCmdShow As Long) As Long
Public Function fAccessWindow(Optional Procedure As String, Optional SwitchStatus As Boolean, Optional StatusCheck As Boolean) As Boolean

If Procedure = "Hide" Then
dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
End If

If Procedure = "Show" Then
dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
End If

If Procedure = "Minimize" Then
dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMINIMIZED)
End If

If SwitchStatus = True Then
If IsWindowVisible(hWndAccessApp) = 1 Then
dwReturn = ShowWindow(Application.hWndAccessApp, SW_HIDE)
Else
dwReturn = ShowWindow(Application.hWndAccessApp, SW_SHOWMAXIMIZED)
End If
End If

If StatusCheck = True Then
If IsWindowVisible(hWndAccessApp) = 0 Then
fAccessWindow = False
End If
If IsWindowVisible(hWndAccessApp) = 1 Then
fAccessWindow = True
End If
End If

End Function


********
then you just need 2 macros
mcrHide
Action = runcode
Function Name = fAccessWindow ("Minimize", False, False)

and mcrRestore
Action = RunCode
Function Name = fAccessWindow ("Show", False, False)

******

Call the macro on form open on your form when the database opens.
I've attached a sample of this with a form, altough I've not set it to open when the database opens.

any problems let me know.

 

[baaz]

Thanx a lot mate. But If I make this macro running when u run the database, how about when you need to edit the database or run it in desginer-mode and want everything to be shown.

I tried to run the sample you made - when I run the macro, it only shows the form and then I press on the access and the window maximize and I can see the access window again. (I think its the point) How would that exactly fit in my form - e.g. users run it and the form will appear and they the close it. Should I combine this with the other "lock" (as mentioned above) - so I run the developer mode by keeping the "shift" button down?

What would you suggest?

 

[baaz]

Guys, I need your help....

What would you do, if you want to use the existing form as search form also... e.g. you type in some parametres in the form and press search button. A list of relevant records will be shown, wherefrom one can choose the appropriate or most likely?

 

[Pete666]

I'll try and cover your queries:

For developer view hold shift, you can also use the taskbar, right click and select restore. F11 will show you the database window.

You only need to have this run once on your first opening form... it will carry forward from then on unless you use reports, hence the mcrRestore is then required but you use the mcrHide on close event of form.

As a standard you would hide the database window and create an onClose event so if a form is closed (via a button or X) it will open a new form.

I'll see if I have a more complete version of this database that may explain this better as a visual.

 

[Pete666]

Try the attached it has a flash form on open which starts the Hidden view. Then you will see that the Navigator has the same plus on close on all forms opens the navigator.

Let me know if you still have problems with it or don't understand how to carry out another function.

Pete

 

[Pete666]

I'll try and cover your queries:

For developer view hold shift, you can also use the taskbar, right click and select restore. F11 will show you the database window.

You only need to have this run once on your first opening form... it will carry forward from then on unless you use reports, hence the mcrRestore is then required but you use the mcrHide on close event of form "I meant Report instead".

As a standard you would hide the database window and create an onClose event so if a form is closed (via a button or X) it will open a new form.

I'll see if I have a more complete version of this database that may explain this better as a visual.

 

[MarvinM]

Pete,

I tried the code you put in the first hidden.zip file. I'm using Access 2007 so it was converted to a .accdb file. When I ran mcrHide, it minimized the Access application to an icon on the taskbar. That's fine except my users will try to restore it from there. So I tried the "Hide" code, fAccessWindow ("Hide", False, False). When I closed the form, I couldn't get it back. I tried opening Access again and then opening the file, but the error message says that "The database has been placed in a state by user 'Admin' on machine... that prevents it from being opened or locked."

Now I think I can still use this code in my application, being careful to always run the mcrRestore macro in the code. But there's a chance that someone might shutdown their laptop or do something that closes the app without that Restore macro getting run.

Just in case something happens and a database gets locked up this way, do you know how to restore or recover it when the Access application window has been hidden and all forms closed?

Thanks.