Validating between form and table 1

[8441]

I have a form with two fields on. Callsign and Time. Callsign is entered manually by the user as is time.

I want to check the callsign entered in the form with the callsign in the logged_on_unit table. If the callsign on the form matches one in the table it checks to see whether there is a logoff time. If so it ignores it. If not then a error message is displayed and the user put back in the callsign field of the form to enter another callsign.

This will be done using VBA code on the beforeupdate event on the callsign field on the form.

I can't get it to read the records in the logged_on_units table. It needs to work is way down the whole table each time.

Your help would be much appreciated.

 

[nickjar2]

something like this:

beforeUpdate(cancel as integ???)

dim db as database
dim sSQL as string
dim rs as recordset

set db = currentdb
sSQL = "select * from table where [callsign] = " & dateField

set rs = db.openrecordset(ssql)

if rs.recordcount < -1 then
ok
else
enter new value
end if

This should work although you will probably need to tweak it as i am doing this through memory.

Nick

 

[8441]

Thanks for this. However the code falls over at the dim db as database with the message User-defined type not declared.

is database a type or is it the name of the database I am working with.

I haven't done much coding in VBA so your help is much appreciated.

 

[nickjar2]

goto a code module and choose tools then references. Scroll down the list until u find Microsft DAO Object library 3.5 (or 3.6). That will sort that problem.

replace
if rs.recordcount < -1 then
ok
else
enter new value
end if

with
if rs.recordcount < 1 then
ok
else
enter new value
end if

Nick

 

[8441]

OK the database bit worked. I am getting an error on the sSQL line. mine reads

sSQL = &quot;select * from Logged_On_Units where [Callsign] = Callsign&quot;

It needs to check the callsign entered on the form with an entry in the table.

The error appears at the line

Set rs.db.openrecordset(sSQL)
The error is typemismatch.

 

[nickjar2]

you want

set rs = db.openrecordset(sSQL)

not
Set rs.db.openrecordset(sSQL)

sSQL = &quot;select * from Logged_On_Units where [Callsign] = Callsign&quot;.. check that your field is called Callsign and try:

sSQL = &quot;select * from Logged_On_Units where [Callsign] = &quot; & callsign

Nick

 

[8441]

Sorry to be a pain. Tried the above. set db is changed to Set db by Visual basic automatically.

I now get an error at the Set rs = db.openrecordset(sSQL)

The full code is below.


Private Sub Callsign_Id_BeforeUpdate(Cancel As Integer)
Dim db As database
Dim sSQL As String
Dim rs As Recordset

Msg = &quot;Unit already logged on, Check Callsign or Logged Unit Off&quot; ' Message to user in box
Style = vbOKOnly + vbCritical ' Define buttons
Title = &quot;GLIMS - Unit Already Logged On&quot;

' Check to see if unit already logged on

Callsign_ToBeLoggedOn = Callsign
'From TEK-TIPS
Set db = CurrentDb
sSQL = &quot;select * from Logged_On_Units where [Callsign] = &Callsign_Id&quot;

Set rs = db.openrecordset(sSQL)

If rs.RecordCount < -1 Then
MsgBox &quot;hello&quot;
Else
DoCmd.CancelEvent
End If

End Sub

The table is called Logged_On_Units and the field I am checking against is Callsign_Id.

I really appreciate your help.

 

[nickjar2]

if the field in the table is called callsign_id then try:

sSQL = &quot;select * from Logged_On_Units where [Callsign_Id] = & &quot; & nameOfFieldOnForm

' where nameOfFieldOnForm is the name of the field on your form eg. txtCallsign


I noticed you had:

&quot;select * from Logged_On_Units where [Callsign] = &Callsign_Id&quot;

there needs to be a space between the & and variable ie
& callsign_id and also the variable must not be within the &quot;.

Nick

 

[nickjar2]

SORRY:

sSQL = &quot;select * from Logged_On_Units where [Callsign_Id] = & &quot; & nameOfFieldOnForm

should read

sSQL = &quot;select * from Logged_On_Units where [Callsign_Id]= &quot; & nameOfFieldOnForm

I had an extra &

Also put

If rs.RecordCount < 1 Then
MsgBox &quot;hello&quot;

Not -1 (my fault again)

Nick

 

[8441]

The sSQL statement appears to be correct. IE it is putting the correct callsign from the form in the &nameoffieldonform bit.

It falls over at set rs = db.openrecordset(sSQL) with the error Run-time error 3061 Too few parameters. Expected 1.

Thanks

 

[nickjar2]

I think u may get this error if a field name is mispelled

Sorry i don't seem to helping.

Nick

 

[nickjar2]

also, is callsign_id text or numeric?

 

[8441]

Callsign_Id is the name of the text box that the user enters the callsign into. This is then used to check whether a callsign already exists in the Logged_On_Units table. The equivilant field is callsign in the table.

Callsign in the table is text as it is made up of two letters and upto three numbers.

 

[nickjar2]

right!!

In that case you SQL will be slightly different.

sSQL = &quot;select * from Logged_On_Units where [Callsign]= '&quot; & callsign_id & &quot;'&quot;

Hopefully we will have success.

Nick

 

[8441]

Many many thanks. That has now worked. I have to do a little more work around the validation but the hardest bit has now been sorted.

Many many thanks
craig