problem with form selection

[jon182]

I have the following tables town, street, postcode, which are linked.
What i am trying to do is in a form when someone enters the post code, the street and town for the post code would be displayed.
Or if someone enters a town, only the streets for that town are displayed.

Im sure there is a way to do this, and would appreciate help!!

Jon

 

[hermanlaksko]

Hi Jon
You need to alter the recordsource for your form after each change in 3 variables. i.e.:

Function Vis_FakturaTotaler(FraNr, TilNr, KundeNr, IntRef, FraDato, TilDato)
Dim MySql As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
Dim Tmp As Variant
On Error GoTo Err_Vis_Fakturatotaler

MySql = "SELECT DISTINCTROW Sum(FakLog.IaltVal) AS S_IALT, Sum(FakLog.KostPr) AS S_KostPr FROM FakLog WHERE "
ArgCount = 0

AddToWhere "'" & Forms!ekspedition!FirmaID & "'", "[Faklog].[FirmaID]", MyCriteria, ArgCount, "="
If Not IsNull(FraNr) Then AddToWhere FraNr, "FakLog.FaktNr", MyCriteria, ArgCount, ">="
If Not IsNull(TilNr) Then AddToWhere TilNr, &quot;FakLog.FaktNr&quot;, MyCriteria, ArgCount, &quot;<=&quot;
If Not IsNull(FraDato) Then AddToWhere &quot;#&quot; & Format(FraDato, &quot;mm-dd-yyyy&quot & &quot;#&quot;, &quot;Faklog.Dato&quot;, MyCriteria, ArgCount, &quot;>=&quot;
If Not IsNull(TilDato) Then AddToWhere &quot;#&quot; & Format(TilDato, &quot;mm-dd-yyyy&quot & &quot;#&quot;, &quot;Faklog.Dato&quot;, MyCriteria, ArgCount, &quot;<=&quot;
AddToWhere KundeNr, &quot;Faklog.KundeID&quot;, MyCriteria, ArgCount, &quot;Like&quot;
AddToWhere IntRef, &quot;Faklog.IntRef&quot;, MyCriteria, ArgCount, &quot;Like&quot;

If MyCriteria = &quot;&quot; Then
MyCriteria = &quot;True&quot;
End If

MyRecordSource = MySql & MyCriteria
Forms!Fakturatotaler.RecordSource = MyRecordSource

End Function


Sub AddToWhere(Field_Value As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer, Argument As String)
Dim ChrStr

If IsNull(Field_Value) Then Exit Sub
If Field_Value = &quot;&quot; Then Exit Sub

ChrStr = Left(Field_Value, 1)

If (ChrStr = &quot;'&quot Or (ChrStr = &quot;#&quot Then
If Len(Field_Value) < 3 Then Exit Sub
End If

If Field_Value <> &quot;&quot; Then
If ArgCount > 0 Then
MyCriteria = MyCriteria & &quot; and &quot;
End If
If Argument = &quot;Like&quot; Then
MyCriteria = (MyCriteria & FieldName & &quot; Like &quot; & Chr(39) & Field_Value & Chr(42) & Chr(39))
Else
MyCriteria = (MyCriteria & FieldName & &quot; &quot; & Argument & &quot; &quot; & Field_Value)
End If
ArgCount = ArgCount + 1
End If

End Sub