combo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My form which is based on a query called INVOICE, has two comdo boxes apart
from other fields. The first combo box named TOID when on click gets down
(lookup) data from a table call PEOPLE, which has a list of people and
another combo box named CONID, which is related, gets details from another
table called CONTRACTS which has various unit lists per person (can have
various units per person). I which that from the first combo TOID the field
choosen from this combo is transfered to the field to combo CONID which in
turn lists all the related units of the choosen PEOPLE field from the first
combo TOID.
I tried the flowing taken from The access Web:-
Private sub cbxTOID_AfterUpdate()
Dim strSQL = "Select" & Me!cbxTOID
strSQL = strSQL & "from PEOPLE"
Me!cbxCONID.RowSourceType = "Table/Query"
Me!cbxCONID.RowSource = strSQL
End Sub
Nothing happened and the combo CONID remind empty and when I click on the
combo box CONID all the units of all the people was given. When you have a
unit list of 500 this becomes imprecatable!!
Also, when I input a new TOID number the CONID returns the previous list.
Can any body help as I just cannot understand what I am doing wrong?
 
I must add that the error I got was User-defined type not defined. What does
this mean
 
First I think you need a few gaps in the strSQL statement. I am guessing you
get something like "SelectMyFieldfrom PEOPLE"
Next stick a break point on the following line and debug the value of strSQL
Me!cbxCONID.RowSource = strSQL
Cut'n'paste the value of strSQL into a new query and run it to make sure you
get the records you expect.
If so, do a Me!cbxCONID.Requery on the combo to update it with the new data.
 
John,
Below find the corrected EVENT in my database

Private Sub cmbOPID_AfterUpdate() , name of combo box1
Dim strSQL As String
strSQL = "SELECT" & Me!cmbOPID
strSQL = strSQL & "FROM frm-Curr(Invoice)"
Me!cmbConID.RowSourceType = "Table/Query" , name of combo box2
Me!cmbConID.RowSource = strSQL
Me!cmbConID.Requery
End Sub

I stii cannot make it work. So simple BUT its back to square one.
In RowSource of combo box cmbCONID I used the SQL query and in total used
the statement Where and in cretaria I did the statement
[forms]![frm-INVOICE].[cmbOPID] where frm-INVOICE is the name of the form I
am using. I run the form it first asked me to input the OPID which must be
from the SQL query of cmbCONID (WHICH I DO NOT REQUIRE AS THIS SHOULD HAVE
BENN AUTOMATICALLY THERE IN THE FIRST PLACE) and I got no field transferred
in cmbCONID. When I view conCONID I got ZERO fields. I am realyy confused.

Thanks in advance for your reply and sorry I did not get to youearlier as I
had to leave my office.
 
Hi, John I left some things out so I am re-writing it again:-
Private Sub cmbOPID_AfterUpdate()
Dim strSQL As String
strSQL = "Select" & Me!cmbTOID
strSQL = strSQL & "FROM firm-Curr(INVOICE)" // input request of person
Me!cmbCONID.RowSourceType = "Table/Query"
Me!cmbCONID.rowSource = strSQL // related field in the find request
Me!cmbCONID.Requery // to clear memory
End Sub
Does this make sense to you now? To me it does not
Philip
 
Back
Top