Access 97:: 7 questions

[accessdummy]

Q1) How do I do a form validation to make sure that all fields are filled in and I want to display friendly messages to let users know what they have to do?

Q2) Is there a faster way to 'empty' the form fields if users decide not to save records?

Q3) How do I change the text input into upper case?

Q4) How do I perform a search by query using a form? Can anyone show me a simple example? When I click on the 'search' button, how does it perform search?

Q5) How do I build expression to have running serial numbers on FORMS? for reports, the control source will be =1 and change the running sums to over all. What about forms?

Q6) How do I logically delete a record and not physically? I know use a checkbox but how?

Q7) If I have a form, the form has a combo box with values as such 'healthy' , 'ok' , 'fair' , 'weak' , 'very weak'. If i were to downgrade the health status, how do I ensure that users will only enter a value lower than its orignal value still using a combo box? E.g: Patient A is healthy, the user want to downgrade Patient A status, user can only choose 'ok','fair','weak','very weak'. Patient B is weak, the user want to downgrade Patient B status, user can only choose 'very weak'. Patient C is very weak, user cannot downgrade anymore further.

 

[dcx693]

At first glance, I'd say all of this is possible, but you really should post these questions separately. Inevitably, the responses will start mixing the answers and it will be very hard to keep track of what has and has not been answered.

 

[accessdummy]

I lump them up together because I don't want to flood the forum.

Maybe you can state the problem number then the solution.

Thanks alot. Need the answers urgently

 

[Mile-O]

I don't have time to answer them all but put together a couple of answers. Hoipe some of these help, or give ideas...

1) My preferred method is to use the Tag property to store a string in. In the Tag property, (for a forename field, for example) I’d put 0forename. 0, to me, means that the data is missing from the field (or not entered) and forename is, obviously, the data I want in the field. After the update of the field I change its tag to 1forename so I know its been updated. If the user then deletes the data I change it back to 0forename. Then, I personally, have a Confirm button which runs code like this:

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acTextBox Then
        If Left(ctl.tag, 1) = "0" Then
            MsgBox "You have not entered the " & Mid(ctl.tag, 2) & ".", vbExclamation, gAPPNAME
            ctl.SetFocus
            Exit Sub
        End If
    End If
Next

2) Me.Undo

3) It depends where you want to do it. As a format in a field you can enter >L as the format which will change text (appearance only, not the actual data) to upper case. In code you have the Ucase() function or the StrConv() function.

6) I prefer, rather than a checkbox, to use a date field called DateExpired in my tables. I then use an UPDATE query to change, on delete, that field to today’s field. The field defaults to #31/12/2200# and all current data, when queried , uses the >Date() criteria.

7) Use the BeforeUpdate() event of the combobox to validate. There are umpteen methods to do this such as using numbers to identify the different states and checking that the state is greater (or less, depending on which way they fade out) and, if the change is not what is wanted then you use the Cancel = True statement to cancel any changes the user has made to the combobox.

 

[jimbrooking]

Q4: In its simplest form, you could have a text box, call it txtLookFor. In its AfterUpdate event, say something like

Me.Filter = "SrchField Like *" & me.txtLookFor & "*"
Me.FilterOn = True

This would filter your recordsource so that it shows only records where the field SrchField has the string in the text box somewhere in it.

A more sophisticated search would let the user search for any word in the text box, for all words, or (as the code above does) the exact phrase.

Q5. Why not have an AutoNumber field do this for you?

 

[WayneRyan]

AD,

3) Use the UCase function (see help)

4) Make an unbound text or combo box and base your form
on a query that uses the text or combo as criteria. There are
plenty of examples here. Do a search on "Search".

Well, that's all 7.

Wayne