Prev: Calculating or Adding Values that are 0
Next: date confusion
From: catalan127 via AccessMonster.com on 2 Feb 2010 11:48
I have two tables - Barcodes (fields: Barcode, Event, Callsign, Registration,
Type, Operator) and Movements (fields: FltDate, Callsign, STA, STD, ATA, ATD,
etc, etc) and a query linking these tables called Logs, which filters out
records showing only those where ATA is blank, or where ATA has a time and
ATD is blank.
Based on the query is a form called Logs which automatically enters the time
in ATA (if the field is blank) or ATD (if ATA has been completed). All this
is functioning well but I need to be able to add a record if I get an
unexpected landing.

My code is as follows:-
Private Sub Barcode_Enter()
DoCmd.GoToRecord , , acFirst
If IsNull(Me.A_T_A) Then
Me.A_T_A = Now()
DoCmd.Save
ElseIf Not IsNull(Me.A_T_A) And IsNull(Me.A_T_D) Then
Me.A_T_D = Now()
DoCmd.Save
Else
Me![Barcode].DefaultValue = Tables!Barcodes!Barcode
Me![CALLSIGN].DefaultValue = Tables!Barcodes!CALLSIGN
Me![OPERATOR].DefaultValue = Tables!Barcodes!OPERATOR
Me![Regn].DefaultValue = Tables!Barcodes!Regn
Me![Type].DefaultValue = Tables!Barcodes!Type

Me.FLTDATE = Now()
Me.A_T_A = Now()

End If
End Sub

I get the following error "The Microsoft Jet Database Engine cannot find a
record in the table 'Barcodes' with key matching field(s) 'CALLSIGN.' "

From what I can tell with similar problems, my form is based on a table that
is on the "many" side of a one-to-many relationship the SQL code is as
follows:-
SELECT Barcodes.Barcode, MOVEMENTS.CALLSIGN, Barcodes.Operator, Barcodes.Regn,
Barcodes.Type, MOVEMENTS.FLTDATE, Barcodes.Event, MOVEMENTS.[S T A],
MOVEMENTS.[S T D], MOVEMENTS.[A T A], MOVEMENTS.[A T D]
FROM Barcodes INNER JOIN MOVEMENTS ON Barcodes.Callsign = MOVEMENTS.CALLSIGN
WHERE (((MOVEMENTS.CALLSIGN)=[me].[filter]) AND ((MOVEMENTS.FLTDATE)
=#3/19/2010#) AND ((MOVEMENTS.[A T A]) Is Null Or (MOVEMENTS.[A T A])>0) AND
((MOVEMENTS.[A T D]) Is Null));

I have tried Left Join and get the same error. Right Join will create the
new record, ie ATA = now but it does not populate the form with the other
fields I need from the Barcodes table, ie Barcode, Callsign, Operator,
Registration or Type.

I am a very much a novice at this so would ask if you could go easy with me
please - any help/advice would be much appreciated. Thank you.

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Calculating or Adding Values that are 0
Next: date confusion