Hello Everyone! I have created a

[abbazabba]

Hello Everyone!

I have created an access database for recording the loaning out of equipment to students.

I have a form with a drop down menu containing all the student names to make entering the information easier.

We run a deposit system, so if students lose their deposit (due to bringing kit back late etc) they cannot borrow equipment.

I'm looking for a way of making a 'sinners' list that have lost their deposit, that is referred to when entering the name on the form. If the name that is entered is on the sinners list, a message pops up saying 'no way!'

Is this possible?

Cheers!

Phil

 

[KenReay]

Hi

The easiest way to do this would be to have a yes / no column in the table of student names, to indicate that a student is a 'sinner'.

Now in the combo box which lists the student names, include the yes/no column in the list of fields selected in the query, increase the .column property by 1, set the width of that column to 0 in the .columnwidths property

Say the column is the third from the left (eg we have StudentId, StudentName, chkSinner), the columns are actually treated as a zero based array so cboName.Column(2) will return the chkSinner value

so, if we put in the after update eevnt of the combo box code like so:

If cboName.Column(2) = True Then
MsgBox "No Way"
Exit Sub
Else
' do whatever
End If

Note cboName is to be replaced with the name of your combo box

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mph1]

Hi Phil,

What you are looking to do IS possible. Based of what you have indicated, I do not have a complete understanding of your policies and/or how a deposit by a student can be lost. I would think the student would deposit X dollars with the school when they take a peice of equipment.
In concept, if a piece of equipment has been taken out by student A, indicate this in the form as to date, piece of equipment and maybe length of time requried and deposit amount and when due back. Each student should have a number. If student A comes back at a later time to borrow another piece of equipment, but has not returned the original piece, then when their recored is brought up you could have a message to indicate such.
You would want to keep a table of students, another table with all of the equipment.
Hope this gives you some idea as to what and how. If you have a specific question on code, etc, please post back.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[scottian]

I think a better method would be to have the form back color to change to lets say red if the loan is overdue. A message box only pops up once and is then discarded. if the back color of the form is changed its more prominent. you also have some other function, such as allowing more loans to be added to the student, to be dissabled if the student is a sinner. this will restrict users from ignoring your rules.

 

[abbazabba]

Hi there!

Thanks to all of you for replying so quickly, some good ideas on how to progress.

I have started down the route of Kenreay's idea (as it was the first!).

I have included a yes/no column called 'sinners' in the list of fields selected in the query, then set the width of the column to 0, but I wasn't sure what you meant by 'increase the .column property by 1'?

I then typed the following code in the after update as an event procedure:

Private Sub STUDENT_NAME_AfterUpdate()
If STUDENT_NAME.Column(2) = True Then
MsgBox "No Way!"
Exit Sub
Else
'Do Whatever
End If

Then, when I enter a student name that is deemed a sinner it brings up the visual basic window with an error

'Compile error: Expected end sub'

Seems like it might work but perhaps i've made a slight mistake!

Any ideas?

Cheers

 

[mph1]

Private Sub STUDENT_NAME_AfterUpdate()
If STUDENT_NAME.Column(2) = True Then
MsgBox "No Way!"
Exit Sub
Else
'Do Whatever
End If


Try the following

Private Sub STUDENT_NAME_AfterUpdate()
If STUDENT_NAME.Column(2) = True Then
MsgBox "No Way!"
Else
'Do Whatever
End If
Exit Sub I moved this to the end


An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[KenReay]

Hi

The error message is telling you exactly the problem!

Private Sub STUDENT_NAME_AfterUpdate()
If STUDENT_NAME.Column(2) = True Then
MsgBox "No Way!"
Exit Sub
Else
'Do Whatever
End If

End Sub <-------------


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mph1]

Please accept my apologies, Ken is correct. I knew what I meant to say, but taliking on the phone and trying to answer a question at the same time, I obviously lost my focus.

Thanks Ken for keeping a watchfull eye.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin

 

[abbazabba]

Hi there,

Of course! Thank you, that's made my day..I had to change the column(2) to .column(1) to make it work, but now it works fine!

Is there a way of changing the text in the message? ie. size and colour? I thought a nice bold red message would look rather cool!

Cheers

Phil.

 

[scottian]

Use a custome form instead.
replace the line

MsgBox &quot;No Way!&quot;

with

DoCmd.OpenForm &quot;MyForm&quot;, acNormal, &quot;&quot;, &quot;&quot;, , acNormal

then create your custom form called &quot;MyForm&quot; with whatever error message you want and whatever format. Big Bright Red Text.

 

[abbazabba]

Hello there

That's great! thanks for your help.

Phil.