Database Program Code Cleanup Help!

[jcwillette]

I have a databae that I would like help in cleaning up the code. When I open up the database using the Splash screen, it is working fine.....Hitting OK takes me to the Switchboard....where everything is working fine....

In the Form button and then the Edit/Enter License Data it opens up to my man form that contains Search For License, License, State, Code 4, Handicapped, Make , Model, Type, and Color. It looks like it has some info from another record and it should be a blank record waiting for data entry! The Dates Subform has the Date, Tiem, Check, Inform, Cit, Boot, Level, Sec, Reason, and a box for Comments. As you can see the Date, Check and Comment "Team Member?" are already inserted. This is fine, but needs to be displayed when the cursor gets to the subform.

Enter 000111, Press Tab and you will get a display box that says not found. this is working great. Hit OK and enter NV for state, Ford for make, Explorer for Model, SUV for Type and Blue for Color, Pressing tab after each entry. Your cursor will be on the date. Enter 28Feb09 and press tab. 1356 and press tab, 5 for Check, tab until the Comment Box. "Team Member? should be highlighted..Press tab. Another record is created...This is working fine but maybe should be displayed until actual data is entered into th esecond subofrm record????

Pressing Ctrl Tab will take you to a new search. Enter 002 and RBR will display...press tab. The record info for 002 RBR will come up an dyour curso will be in the new date box on the subofrm awaiting new info....theis is working great. Pressing a Ctrl Tab will take you back to the Search box where it looks like another recod is being displayed in red along with all the subfoprm data for that license....this should be blank on both forms awaiting new input.

If anyone can hel I would appreciate it. I have included a small portion of the database for assistance...Thanks.

JC

 

[wazz]

i recommend you edit your post down to one question and put the rest into new, separate threads. work on each problem and post questions that show what you have tried to do and any errors you may be getting.

 

[jcwillette]

I was hoping that both these problems wre related since they are happening in the same form and subform...Maybe someone could give it a quick look over and head me in the right direction...

Thanks

JC

 

[Mike375]

I agree with wazz

and post questions that show what you have tried to do and any errors you may be getting

Unless someone comes along in the right frame of mind and who is specifically interested in this, then it is all too hard.

As wazz said have a go yourself at whatever you are trying to do then post up a specific problem as you encounter them.

 

[wiklendt]

first, you need more help than just the code. before you distribute this database anywhere i would suggest some important things:

1) see attachment 1. when i open your first two forms (the splash and then the switch) neither are displayed in the middle of the screen, which i find annoying and your users may also find it annoying. mine is a 14.1" widescreen with resolution 1280x800, and your forms appear towards the bottom left corner of my view. (attachments 2 and 3 i had to crop to fit it under 100kb limit for image attachments.)

2) see attachment 2. i cannot at all read the blue writing on the grey background - it's awful, i can barely make out what the data is.

3) see attachment 3. when i choose a license from your dropdown, i can't get back to any other forms... i'm stuck in that one. if i want to get back to the other forms, i have to close that form, which isn't obvious from the way its setup that i can get back to the switchboard that way.

3b) after i enter 000111, it tells me there is no license in the database, but it doesn't tell me anything else - is it going to enter this license INTO The database for me? do i have to chose another license because this one doesn't exist?

4) see attachment 4. i tried two most common methods of date entering in your license form. neither were accepted and i wasn't sure what your input mask actually needs to accept a date format. i would suggest that instead of using an imput mask, that you make this field a date field and simply apply a format for the data after it's entered. i don't see and ordinary user knowing what an input mask is and how to de-code what they're supposed to do. my third attempt i decided to enter the date EXACTLY as you had written it in your first post "28Feb09", which is really non-intuitive for most people.

5) see attachment 5. so then i added 1356 in the time field JUST like you had in instructed your post, and i got the error you see on attachment 5 picture.

6) pressing CTRL+TAB to get back to the search is too convoluted - no ones going to remember that if they don't have the VERY DETAILED instructions in front of them. i suggest you leave the search there. (this is why i felt stuck in point (3))

7) pressing tab doesn't seem to be necessary at all. in fact you have a message on the form to use ctrl+tab, which seems really useless also, as i had no problems entering the data using just the mouse. leave this instruction out and let people enter the data how they want.

8) after i entered the data for 000 111, the search combo could not find it.

9) if i enter 002rbr instead of 002[space]rbr, the database assumes this is a new license. i presume this is INcorrect. i presume the space is just to make it easier to read, not as part of the license number itself. like the date field, this should not be part of the data, but part of the formatting. make the data "###XXX", then when it's displayed make the format have that space in it. people (me) don't generally put the space into a license number, at least, i wouldn't have ever thought it would be necessary.

10) i can't help you with the problem you mentioned in your last sentence because i have no idea where your code is for this "CTRL+TAB" business - you need to give more detail about where your code is, what you want it to do, what you've tried to do that hasn't worked. etc.

11) i hope your clients know what kind of parameters they need for the reports, 'cause i certainly didn't and there're no clues at all given in the database unless you know it intimately.

 

[wiklendt]

i also didn't want to say this in my first post because i didn't want to upset you too much, but i feel that i really can't go on without mentioning it:

google "form aesthetics" - you need to update the look and feel - at the current style, it looks like a program made for a win95 application. the colours clash and the controls are scattered in a messy way. tidy up the look as well as the code, and you'd have a usable db.

end clients will NOT like/want to use your database (or anyone's database) if they don't like the look of it. i'm not saying make it 'fancy' or add a million bells and whistles, i'm saying that you need to make it at least aesthetically bearable.

my supervisor couldn't even tell there was the data "Team Member?" in those fields.

 

[jcwillette]

wiklendt,

1) I have placed all the screens to the left and towards the top so that the client (myself and a couple of folks) can use it on the computer at work that is set at 800x600. I hope that this will not bother you when trying to help.

2) The colors I have chosen are working fine on my home computers and the ones at work including a lap top. They are open for changes but they are not the problem that I have asked to be looked at.

3) If you follow the4 directions that I have given, you will see that if you type in the 000 111 it will add it to the database and you can track it on future days. By the way, it will beep to let you know that it is not in the database so you don't have to guess if it is or not. When you type in the 002 RBR the full license will display with only a few key strokes...when you tab all the rrecords come up for it....this saves a lot of keystrokes.

4) Since only 3 of us will be using the database and all three of us are prior or active military, I went with military dates without the spaces to save on keystrokes. Hence the 28feb09 will give the correct date. The field is a date field with an input mask of the military date without the spaces.

5) The time is the same as the date. It is a time field with military time without the colon to save keystrokes.

6) If the CTRL + TAB isn't convient then what would you suggest to get back to the search field? I figured that this is a conventional key stroke for access and was alright to follow.

7) I can see that it would take a long time to enter the almost 6000 records using the mouse. I am trying to set this database up using teh fewest keystrokes and no mouse movememts as it wastes time.

8) That is right as it is a new license in the database as the message box indicates along with the beep.

9) I guess you need to look at some license plates. a 002RBR is not the same as 002 RBR....they are two different plates and will be treated as that. Some licenses are 888 9999, ABC 123, HW 89 HY65....you have to be able to input almost everything so there is no formatting that can be done.

10) You guessed it....I don't have any CTRL + TAB business cause it is a standard keystroke for access.....I will try to post the code in my next message.

Thanks

JC

 

[jcwillette]

Here is the code for the Inputform located in the Search under the After Update:



Option Compare Database
Option Explicit
Private Sub Form_GotFocus()
DoCmd.Maximize
Me.Form.Requery
Search = ""
License = ""
DoCmd.GoToRecord , , acNewRec
Me.Search.SetFocus
End Sub
Private Sub License_BeforeUpdate(Cancel As Integer)
If MsgBox("Do you want to change this License???", vbYesNo + vbDefaultButton2, "BE CAREFUL!!") = vbNo Then
Me.Undo
Exit Sub
End If
End Sub

Private Sub Search_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Search) Then
' Save before move.
If Me.Dirty Then
Me.Undo
Me.Dirty = False
End If

' Find the Licene that matches the Search String.
Set rs = Me.Recordset.Clone
rs.MoveFirst
rs.FindFirst "[License] = '" & Me![Search] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
If rs.NoMatch Then
' Make user aware of new License
DoCmd.Beep
' Take Search results and place them in the License box
DoCmd.GoToRecord , , acNewRec
License = Search
Me.State.SetFocus
MsgBox "License NOT found in Database!"

Else
' Display record in the form
Me.Bookmark = rs.Bookmark
Forms!InputForm!DateSform.SetFocus
DoCmd.GoToRecord , , acNewRec
Forms!InputForm!DateSform.Form.Controls!DateBox.SetFocus

End If
Set rs = Nothing

End If
End Sub

 

[jcwillette]

I got it runing......Thanks to all who helped...

JC

 

[wiklendt]

would you mind posting the solution so that others may benefit...?

 

[wiklendt]

1) I have placed all the screens to the left and towards the top so that the client (myself and a couple of folks) can use it on the computer at work that is set at 800x600. I hope that this will not bother you when trying to help.

that's fine, however, you did not say there were only three buddies using this with idential screens and resolutions. i merely wanted to point out what seemed to me as a design error - i was not to know that you were not going to be passing this DB on who may have a different screen to the one you have specifically made your DB for.

2) The colors I have chosen are working fine on my home computers and the ones at work including a lap top. They are open for changes but they are not the problem that I have asked to be looked at.

i apologise for bringing to your attention the fact that other people may not physically be able to see the data in your database. if you and your buddies are happy with that, then fine. i just wanted you to develop a database for a wider audience whom you may not know. since you have pointed out that you are only a small group of people and everyone's happy, i suppose that's not an issue. you didn't say it in the first instance, so how was i to know.

3) If you follow the directions that I have given, you will see that if you type in the 000 111 it will add it to the database and you can track it on future days. By the way, it will beep to let you know that it is not in the database so you don't have to guess if it is or not. When you type in the 002 RBR the full license will display with only a few key strokes...when you tab all the rrecords come up for it....this saves a lot of keystrokes.

well, i usually have my computer set to mute, so as not to annoy the other commuters on the train and colleagues in the lab office. the fact that it beeps tell me nothing. and the message box isn't helpful. again, if there are only three of you and you all know what the deal is, then fine, but if you were to distribute this DB to a wider audience, a lot of people would become confused.

4) Since only 3 of us will be using the database and all three of us are prior or active military, I went with military dates without the spaces to save on keystrokes. Hence the 28feb09 will give the correct date. The field is a date field with an input mask of the military date without the spaces.

ok, again that's fine, since all of you to be using the DB are happy about it. and how in heck was i supposed to know any of that was military? i've never been in the military, of any country, so any of your 'violations' terms and things are completely foreign to me.

5) The time is the same as the date. It is a time field with military time without the colon to save keystrokes.

that's just the issue: i DID put it all in 'military' (i.e., 24-hour time, for everyone else) style and without the colon - and it still gave me that error. even though you DID NOT ASK FOR HELP on THIS issue, i thought, in good faith, that i ought to point it out to you.

6) If the CTRL + TAB isn't convient then what would you suggest to get back to the search field? I figured that this is a conventional key stroke for access and was alright to follow.

my suggestion is [edited word order] to not hide [/edit] the search field at all. not sure what you mean by 'convetional key stroke for access'...?

7) I can see that it would take a long time to enter the almost 6000 records using the mouse. I am trying to set this database up using teh fewest keystrokes and no mouse movememts as it wastes time.

first, your instructions said EXPLICITLY to use the TAB key. this implies that it will not work any other way. your instructions to US was to enter just ONE record - i have no dispute that to enter 6000+ records tabbing will be a good solution, but again, you did not specify this. you COULD have said "enter this data - for conventience, you can use the tab key if you're entering 6000+ records to make it faster" rather than "..then press tab, then enter blah, then press tab, then enter blah, then press tab..."

people know that, generally, tab is a way to move through the fields of a form. if you say EXPLICITLY to use the tab key, then it can be assumed that you hav ecoded the DB specifically only to accept the data if only the TAB key is pressed. this was not the case, so i thought i should point it out to you IN GOOD FAITH... sir.

8) That is right as it is a new license in the database as the message box indicates along with the beep.

the message does NOT indicate that this it is creating a new record/license. what it specifically says is that this license is not already in the database - that's two VERY different scenarios. you have to understand at this point that many people code their DB TO NOT ALLOW further entries, so this message box could easily confuse (however, again, if your group of intimate three are happy with this, that's FINE)

9) I guess you need to look at some license plates. a 002RBR is not the same as 002 RBR....they are two different plates and will be treated as that. Some licenses are 888 9999, ABC 123, HW 89 HY65....you have to be able to input almost everything so there is no formatting that can be done.

i looked at ALL your license plates before i started. they ALL had the format XXX[space]XXX - which is the ONLY reason i mentioned it. i would NOT have said anything if there were even the small number of variations that you have just now decided to inform us of.

again, you may not have asked for this, but i just wanted to point this out to you for making everything easier - besides, i thought you wanted military execution WIHTOUT superfluous keystrokes??? in which case leaveing off the spaces at data entry would still be a valid option - the only question would be whether there is the possibliity that license "000 111" could exists as well as "000111" - surely whoever it is that creates these licenses must be aware that those two licenses are extremely close in nature?

10) You guessed it....I don't have any CTRL + TAB business cause it is a standard keystroke for access.....I will try to post the code in my next message.

i don't know about this keystroke or what it does or how it functions. and i do apologise for not being able to help you on this point.

i would urge you to revisit these point if ever you decide that your DB needs to be distributed to a wider audience. at the moment, if it's working for you, then ok. but please don't be surprise if one day someone comes to you asking again how to use this or that or saying there are bugs in it...

 

[wiklendt]

LOL, now that i know you're military, it makes sense to me that you would write your first post the way you did