Lost_Focus Error (1 Viewer)

[Reflex_ht]

Hy,

I have made several DB for my Company. In the most of them i use the Lost_Focus event to start a LookUp for a Match so that: for an example the user enter only a match and the fields for another Company Name, Adress, PLZ, Land... will be filled automaticaly. Everithing is working greate but because I use a Split form they always have a error message when they try to filter the Row that contains the Match data.

I tried to solw the problem with changing the event. Now its the On_exit event but the problem stays. The best solution that i have is to make for every "match row" a "Clone-row" that is equal to the "match row" and in the split form i hid the actual "match row". The users can filter the cloned rows without any issue and everithing is working fine but isn´t there any bether/ "smarther" way to solwe that? thx

 

[boblarson]

Say it with me - AFTER UPDATE, AFTER UPDATE, AFTER UPDATE. Use the After Update event.

Second, Split forms have issues. They seem to cause more problems than they are worth. You can build your own "split form" essentially by using an unlinked subform.

 

[spikepl]

 

[boblarson]

..........

............

..........

...........

..........

 

[Reflex_ht]

After update, after update, after update,after update, after update, after update :d

 

[Reflex_ht]

THX for the Reply. But when i made that "match" i tried every event and the Lost_Focus and On_Exit are doing what i want. After_update is not doing the work like it should. Sometimes it does the LookUp sometimes not.

I´m blessed with Users wich make lots of mistakes so that i become very fast replyes when something is wrong. Even if i tested the thig thousand times, they find something in the first 5 min i tried it now with the AfterUpdate and it´s not that what i/they want

 

[pr2-eugin]

Show the code you have on the AfterUpdate event.. Also have you deleted the lines of code from the LostFocus and OnExit events??

 

[Reflex_ht]

Private Sub Match_AfterUpdate ()

On Error GoTo ErrH

If Me.Firma.Value <> "" Or Me.Firma.Value <> Null Then GoTo 1
If Me.Strasse.Value <> "" Or Me.Firma.Value <> Null Then GoTo 1
If Me.PLZ.Value <> "" Or Me.Firma.Value <> Null Then GoTo 1
If Me.Ort.Value <> "" Or Me.Firma.Value <> Null Then GoTo 1
If Me.Land.Value <> "" Or Me.Firma.Value <> Null Then GoTo 1

2:
If IsNull([Match]) = False Then
Firma = DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]")
Strasse = DLookup("[Straße]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]")
PLZ = DLookup("[PLZ]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]")
Ort = DLookup("[Ort]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]")
Land = DLookup("[Land]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]")
'Me.Firma.SetFocus
GoTo 3
Else

GoTo 3
End If

1:
If Me.Firma.Value = DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") Then GoTo 3
If Me.Strasse.Value = DLookup("[Straße]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") Then GoTo 3
If Me.PLZ.Value = DLookup("[PLZ]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") Then GoTo 3
If Me.Ort.Value = DLookup("[Ort]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") Then GoTo 3
If Me.Land.Value = DLookup("[Land]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") Then GoTo 3

Response = MsgBox("Wollen Sie das die Adressen übernommen werden? ", vbYesNo + vbQuestion, "Übernehmen")
    If Response = vbYes Then
    GoTo 2
    Else
    GoTo 3
    End If
3:

'ErrorHandler
Exit_ErrH: ..... and so one...

 

[pr2-eugin]

GoTo Statements are great when used properly (i.e. very rarely maybe to handle errors)... It is hard to understand the logical flow of what you are doing.. You are asking to GoTo a specific set of lines then you are asking to GoTo few other lines of code.. Makes it a bit more messy.. My advice is as follows,

1. Eliminate all GoTo use If statements properly..

2. What are you going to do if Me.Strasse is NULL?? To check for Null and empty string use either Nz(Me.Strasse,"") <> "" or Len(Me.Strasse & vbNullString) = 0

3. When using DLookUp's that use the criteria that involves Form control you have to concatenate the values.. not like this..

DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]")

It shoule be something like..

' [COLOR=SeaGreen][B]if QMatch is of Numeric type[/B][/COLOR]
DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]= " & forms!Dispoliste![Match])
' [COLOR=SeaGreen][B]if QMatch is of Text type[/B][/COLOR]
DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]= [B][COLOR=Red]'[/COLOR][/B]" & forms!Dispoliste![Match] &"[B][COLOR=Red]'[/COLOR][/B]")
' [COLOR=SeaGreen][B]if QMatch is of Date/Time type[/B][/COLOR]
 DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]= [B][COLOR=Red]#[/COLOR][/B]" & forms!Dispoliste![Match] &"[B][COLOR=Red]#[/COLOR][/B]")

Please make these changes, then post back..

 

[nanscombe]

Rather than using several different DLookup statements I would tackle this using a ComboBox.

The user can still type in the box and choose the Company they want but the rest of the address information is already loaded into other columns of the ComboBox which I can then copy elsewhere as needed.

I have put together a little demo of what I am talking about.

I hope it is useful.

 

[Reflex_ht]

Thx "pr2-eugin" i´ll clean up the code as son as I have time for it. I´know that my code is very messi but made it under lots of time pressure so that the main goal was to make it work hoe the users wanted it to work. The cleaning procedure is realy neccesery (not only for that function)

Thx "nanscombe" for the sugestion but that is to complicated for the users. This match function is only a little part of a very Big form. The DB is for Logistik Dispechers with difficult wisches. They want to se only the main main data but in the same time all data it depends and they change the data very often. To start the match with a button is no solution for me but i think it is a greate idea for other things i´m working on.

I think that the problem is not in the Lost_Focus or On_exit event. It seams it´s worse. The Splitform is the Problem-maker. I notices some ishiues on the begining but because I started working with Access 4 months ago it was my only solution. When i find more time i will change the form to an normal form with a subform.

Thanks again "nanscombe" for your demo. I´m aktualy working on a new DB where it will be very useful

 

[Khalid_Afridi]

Goto 1 goto 2 goto 3... if if if if if if......
the first time I see this code in my life...
so I will go AFTER Update, After Update....

 

[Reflex_ht]

OK. Thx all of you for the replys I now found time to cleanup the code. Is it like this bether?

' This function watches should the match change the adresse or not (if one is already tiped in)
Private Sub Match_AfterUpdate()
If Me.Firma.Value <> "" Or Me.Firma.Value <> Null Or Me.Strasse.Value <> "" Or Me.Firma.Value <> Null Or Me.PLZ.Value <> "" Or Me.Firma.Value <> Null Or Me.Ort.Value <> "" Or Me.Firma.Value <> Null Or Me.Land.Value <> "" Or Me.Firma.Value <> Null Then
Call AdressenNA
Exit Sub
End If
If IsNull([Match]) = False Then Call AdressenA
End Sub

'This function watches if the adress witch is tiped in is the adress that we are looking for or not (if the one that we are loking for is in then it exits the funktion and else it calls the funktion that inserts a new adress)
Public Function AdressenNA()
If Me.Firma.Value = DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") And Me.Strasse.Value = DLookup("[Straße]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") And Me.PLZ.Value = DLookup("[PLZ]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") And Me.Ort.Value = DLookup("[Ort]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") And Me.Ort.Value = DLookup("[Ort]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") And Me.Land.Value = DLookup("[Land]", "dbo_Beladeorte", "[QMatch]=forms!Dispoliste![Match]") Then Exit Function
Response = MsgBox("Wollen Sie das die Adressen übernommen werden? ", vbYesNo + vbQuestion, "Übernehmen")
If Response = vbYes Then Call AdressenA
End Function

'This function inserts the actual adress parameters
Public Function AdressenA()
'Hier werden die Adressen Daten aufgrund des Matches übernomen
Firma = DLookup("[Namx]", "dbo_Beladeorte", "[QMatch]= '" & Me.Match & "'")
Strasse = DLookup("[Straße]", "dbo_Beladeorte", "[QMatch]= '" & Me.Match & "'")
PLZ = DLookup("[PLZ]", "dbo_Beladeorte", "[QMatch]= '" & Me.Match & "'")
Ort = DLookup("[Ort]", "dbo_Beladeorte", "[QMatch]= '" & Me.Match & "'")
Land = DLookup("[Land]", "dbo_Beladeorte", "[QMatch]= '" & Me.Match & "'")
End Function

It works like the old code but ofcourse it´s mutch more cleaner. Does enyone have more sugestions for that code? Thx again for the help

And i didn´t fogot AFTER UPDATE; AFTER UPDATE ....

 

[Khalid_Afridi]

I always go AFTER UPDATE AFTER UPDATE :d
good you cleaned it out BEFORE UPDATE... ;P

 

[JHB]

How many times do you want to check if Me.Firma.Value <> Null

Private Sub Match_AfterUpdate()
If Me.Firma.Value <> "" Or Me.Firma.Value <> Null Or Me.Strasse.Value <> "" Or Me.Firma.Value <> Null Or Me.PLZ.Value <> "" Or Me.Firma.Value <> Null Or Me.Ort.Value <> "" Or Me.Firma.Value <> Null Or Me.Land.Value <> "" Or Me.Firma.Value <> Null Then

 

[Reflex_ht]

How many times do you want to check if Me.Firma.Value <> Null

THX

I didn´t notice that. I have to test if it is like "" or NUll but for sure not so often

thx again

 

[Reflex_ht]

After a while the code has adwanced

Private Sub Match_AfterUpdate()
On Error GoTo EH
Dim rs As Recordset

If Me.Match.Value Like Me.Match.OldValue Then Exit Sub

Set rs = CurrentDb.OpenRecordset("SELECT COUNT(qmatch) as Anzahl FROM dbo_Beladeorte WHERE [qmatch]= '" & Me.Match & "' ;")
If rs!Anzahl = 1 Then
Set rs = CurrentDb.OpenRecordset("SELECT Namx,Straße,PLZ,Ort,Land FROM dbo_Beladeorte WHERE [qmatch]= '" & Me.Match & "' ;")
Me.Firma = Nz(rs!Namx, "")
Me.Strasse = Nz(rs!Straße, "")
Me.PLZ = Nz(rs!PLZ, "")
Me.Ort = Nz(rs!Ort, "")
Me.Land = Nz(rs!Land, "")
Else
Me.Firma = ""
Me.Strasse = ""
Me.PLZ = ""
Me.Ort = ""
Me.Land = ""
End If

rs.Close
Set rs = Nothing

'ErrorHandler
EHX:
Exit Sub
EH:
Call ErrorHandling.ErrorLog(Nz(Err.Description, "Keine Beschreibung vorhanden"), Nz(Err.Number, 0), Nz(Me.Name, "Unbekannt"), "Match_AfterUpdate")
Resume EHX
End Sub

Please tell me if that code can me made bether.