Combo Box in a endless Formular

[MarkWright]

Hello,
I am using an ADP Databank with msSQL 2000.
I have a formular and call another formular from the main formular. I am filling and filtering the second formular with a recordsource. what I need is a way to use a combobox with J,N,O where I can decide in each line/record which letter I want. the recordsource and combobox information will then be saved in a new Table. I have not found out how to fill each combobox with the letter I want. once I chose a letter all comboboxes are filled. I need a way to choose each combo box separately. any Ideas ?
I have no clue right now.
thanx
Mark
so far I have written this--->

Bez = DLookup("[Bezeichnung2]", "KHKArtikel", _
"[Bezeichnung1] = '" & Me!Produkt1 & " " & Me!Release1 & "'&quot
If IsNull(Bez) Then
'Me!lstTestProbleme.RowSource = " "
Me.Form.RecordSource = " "
Exit Sub

Else

Bez = IIf(InStr([Bez], ";&quot, Left([Bez], InStr([Bez], ";&quot - 1), [Bez])

End If

Me.RecordSource = "SELECT dbo.PBS_TestzyclusProblemNr.testNr," & _
" dbo.PBS_Probleme.ProblemNr,
dbo.PBS_Produkt.Kurzname," & _
" dbo.PBS_Probleme.System, dbo.PBS_Probleme.Release," & _
" dbo.PBS_Probleme.ErfasstAm, dbo.PBS_TestzyclusProblemNr.Uebernehmen" & _
" FROM dbo.PBS_TestzyclusProblemNr RIGHT OUTER JOIN" & _
" dbo.PBS_Probleme INNER JOIN dbo.PBS_Produkt ON" & _
" dbo.PBS_Probleme.ProduktNr = dbo.PBS_Produkt.ProduktNr ON" & _
" dbo.PBS_TestzyclusProblemNr.ProblemNr = dbo.PBS_Probleme.ProblemNr" & _
" WHERE (dbo.PBS_Produkt.Kurzname = '" & Me!Produkt & "') AND " & _
" (dbo.PBS_Probleme.Release = '" & Me!Release & "')AND " & _
&quot; (dbo.PBS_Probleme.ErfasstAm <= '&quot; & Me!Datum & &quot;') AND &quot; & _
&quot; (dbo.PBS_Probleme.ErfasstAm >= '&quot; & Bez & &quot;')&quot;

 

[MarkWright]

Hello, found my problem. I call a recordset to fill my table a first time and then a recordsource. And since I am calling from a query you have to set a destinct Table to save the information. Thats just one of the many differences between a mdb and a adp/SQL 2000 Server.
if anyone is interested here is the hole code I use to fill a table and change information in my Formular
******************************************************
Private Sub Form_Open(Cancel As Integer)
Forms.Item(&quot;frmPBS_TestzyclusProblemNrlist&quot.Caption = &quot;ProblemNr List&quot;
Dim TNr
Dim Bez
Dim rst As New ADODB.Recordset

Dim tbl As New ADODB.Recordset


'*** fill fields to get information needed for Me.RecordSource
If IsNull(Me![testnr1]) Or Me![testnr1] = &quot;&quot; Then
Me![testnr1] = Forms(&quot;frmPBS_Testzyclus&quot![testnr]
End If
If IsNull(Me![Status]) Or Me![Status] = &quot;&quot; Then
Me![Status] = Forms(&quot;frmPBS_Testzyclus&quot![Status]
End If
If IsNull(Me![Produkt]) Or Me![Produkt] = &quot;&quot; Then
Me![Produkt] = Forms(&quot;frmPBS_Testzyclus&quot![Produkt]
End If
If IsNull(Me![Produkt1]) Or Me![Produkt1] = &quot;&quot; Then
Me![Produkt1] = Forms(&quot;frmPBS_Testzyclus&quot![Produkt]
End If
If IsNull(Me![Release]) Or Me![Release] = &quot;&quot; Then
Me![Release] = Forms(&quot;frmPBS_Testzyclus&quot![System]
End If
If IsNull(Me![Release1]) Or Me![Release1] = &quot;&quot; Then
Me![Release1] = Forms(&quot;frmPBS_Testzyclus&quot![System]
End If
If Me!Status = &quot;E&quot; And IsNull(Me![Datum]) Or Me![Datum] = &quot;&quot; Then
Me![Datum] = Forms(&quot;frmPBS_Testzyclus&quot![Datum]
Else
Me!Datum = Me!Heute
End If

'***Get the list of ProblemNr for the Testzyclus
Select Case Me!Release1
Case &quot;4.7&quot;
Me!Release1 = &quot;enterprise&quot;
Case &quot;4.0B&quot;
Me!Release1 = &quot;4.0&quot;
Case &quot;[4.6 c]&quot;
Me!Release1 = &quot;4.6C&quot;
End Select

Select Case Me!Produkt1
Case &quot;Analyzer&quot;
Me!Produkt1 = &quot;[Database Analyzer]&quot;
Case &quot;[Analyzer Plus]&quot;
Me!Produkt1 = &quot;[Database Analyzer Plus]&quot;
End Select


Bez = DLookup(&quot;[Bezeichnung2]&quot;, &quot;KHKArtikel&quot;, _
&quot;[Bezeichnung1] = '&quot; & Me!Produkt1 & &quot; &quot; & Me!Release1 & &quot;'&quot
If IsNull(Bez) Then
Me.Form.Recordset = &quot; &quot;
Exit Sub
Else
Bez = IIf(InStr([Bez], &quot;;&quot, Left([Bez], InStr([Bez], &quot;;&quot - 1), [Bez])
End If

tbl.Open &quot;PBS_TestzyclusProblemNr&quot;, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If tbl.EditMode = False Then

TNr = DLookup(&quot;[testnr]&quot;, &quot;PBS_TestzyclusProblemNr&quot;, &quot;[testnr] = '&quot; & Me!testnr1 & &quot;'&quot

If TNr <> &quot; &quot; Then
'*** read table information
Me.RecordSource = &quot;SELECT dbo.PBS_Probleme.ProblemNr, dbo.PBS_Produkt.Kurzname,&quot; & _
&quot; dbo.PBS_Probleme.System, dbo.PBS_Probleme.Release,&quot; & _
&quot; dbo.PBS_Probleme.ErfasstAm, dbo.PBS_TestzyclusProblemNr.Uebernehmen&quot; & _
&quot; FROM dbo.PBS_TestzyclusProblemNr RIGHT OUTER JOIN&quot; & _
&quot; dbo.PBS_Probleme INNER JOIN dbo.PBS_Produkt ON&quot; & _
&quot; dbo.PBS_Probleme.ProduktNr = dbo.PBS_Produkt.ProduktNr ON&quot; & _
&quot; dbo.PBS_TestzyclusProblemNr.ProblemNr = dbo.PBS_Probleme.ProblemNr&quot; & _
&quot; WHERE (dbo.PBS_Produkt.Kurzname = '&quot; & Me!Produkt & &quot;') AND &quot; & _
&quot; (dbo.PBS_Probleme.Release = '&quot; & Me!Release & &quot;')AND &quot; & _
&quot; (dbo.PBS_Probleme.ErfasstAm <= '&quot; & Me!Datum & &quot;') AND &quot; & _
&quot; (dbo.PBS_Probleme.ErfasstAm >= '&quot; & Bez & &quot;') AND &quot; & _
&quot; (PBS_TestzyclusProblemNr.Uebernehmen <> 'Z' AND &quot; & _
&quot; PBS_TestzyclusProblemNr.Uebernehmen <> 'N' OR &quot; & _
&quot; PBS_TestzyclusProblemNr.Uebernehmen IS NULL) &quot; & _
&quot; order by dbo.PBS_Probleme.ProblemNr desc&quot;

Else
'***look up information and save in Table
rst.Open &quot;SELECT dbo.PBS_Probleme.ProblemNr, dbo.PBS_Produkt.Kurzname,&quot; & _
&quot; dbo.PBS_Probleme.System, dbo.PBS_Probleme.Release,&quot; & _
&quot; dbo.PBS_Probleme.ErfasstAm, dbo.PBS_TestzyclusProblemNr.Uebernehmen&quot; & _
&quot; FROM dbo.PBS_TestzyclusProblemNr RIGHT OUTER JOIN&quot; & _
&quot; dbo.PBS_Probleme INNER JOIN dbo.PBS_Produkt ON&quot; & _
&quot; dbo.PBS_Probleme.ProduktNr = dbo.PBS_Produkt.ProduktNr ON&quot; & _
&quot; dbo.PBS_TestzyclusProblemNr.ProblemNr = dbo.PBS_Probleme.ProblemNr&quot; & _
&quot; WHERE (dbo.PBS_Produkt.Kurzname = '&quot; & Me!Produkt & &quot;') AND &quot; & _
&quot; (dbo.PBS_Probleme.Release = '&quot; & Me!Release & &quot;')AND &quot; & _
&quot; (dbo.PBS_Probleme.ErfasstAm <= '&quot; & Me!Datum & &quot;') AND &quot; & _
&quot; (dbo.PBS_Probleme.ErfasstAm >= '&quot; & Bez & &quot;')&quot; & _
&quot; order by dbo.PBS_Probleme.ProblemNr desc&quot;, CurrentProject.Connection, adOpenKeyset, adLockOptimistic


rst.MoveFirst

Do While Not rst.EOF

If testnr1.Value <> &quot;&quot; Then tbl![testnr] = testnr1.Value
If rst!ProblemNr.Value <> &quot;&quot; Then tbl![ProblemNr] = rst!ProblemNr.Value
If Uebernehmen.Value <> &quot;&quot; Then tbl![Uebernehmen] = Uebernehmen.Value


tbl.Update
tbl.AddNew

rst.MoveNext

Loop
End If
End If

TNr = &quot;&quot;

Me.Refresh
'rst.close
End Sub