combobox

[cybertyk]

ok ill try and word this the best i can this is the last probleam i have, i have a form and on the form is a tab control tabpage one is customer, tabage 2 is contacts for that customer, they are set up in a relationship whey by there can be many contacts for one customer. tabpage2 is a subform.

what i want to do is list all of the contacts name in a combo box on tabpage1 this is easenough done however it lists ALL contacts for all customers, i only want it to show the relvent data from the current field and im really stuck on how to do this i think i need to program it in and i would look up on the forum search but i have no idea what im looking for


please help me once again


thank you

 

[DCrake]

do a search on cascading comboboxes

 

[vbaInet]

You basically want to synchronise that combo box of contacts with the customer?

Is there a CustomerID in your table? Can that ID be editted or it's an autonumber field?

 

[vbaInet]

do a search on cascading comboboxes

From pbaldy - http://baldyweb.com/CascadingCombo.htm

 

[cybertyk]

ok i understand this
Dim strSource As String

strSource = "SELECT City " & _
"FROM Cities " & _
"WHERE State = '" & Me.cboState2 & "' ORDER BY City"
Me.cboCities2.RowSource = strSource
Me.cboCities2 = vbNullString

how ever iwould need it to do this
Dim strSource As String

strSource = "SELECT Title FName Lname Position FROM tblContact " & _
"WHERE State = '" & CURRENT RECORD OPEN & "' ORDER BY LName"
Me.cboMail.RowSource = strSource
Me.cboMail = vbNullString

what is the part for current Record open?

VBAINET, my savour lol

the tables are as follows

Customer Contact
ID <1 --------- ID
Customer Name |------ 8 CustomerID
ECT ECT

 

[vbaInet]

First of all, the CustomerName cannot be your ID in the Contact field. Two or more people can have the same name, hence, it wouldn't be unique

 

[cybertyk]

oh it hasent posted it right

the contact table has the following
ID Autonumber
CustomerID (for linking to [Customer]![ID] using a relationship with the join and child/master setup)
Title
FName
LName
ECT

thats how its ment to be right so that there are MANY Contacts to ONE Customer

Right!?!

 

[vbaInet]

Ok good

Dim strSource As String

strSource = "SELECT Title, FName, Lname, Position FROM tblContact " & _
            "WHERE CustomerID = " & nz(Me.CustomerID, 0) & " ORDER BY LName, FName"
Me.cboMail.RowSource = strSource
Me.cboMail = vbNullString

 

[cybertyk]

ok i can see the code i keep seeing nz what does that mean? and when you tell me please be gental lol

 

[vbaInet]

The Nz() function will handle null values. If your textbox or field is null and you try to run that code, it will fail so the Nz() function has a second parameter where you can substitute that null value for something else that is compatible.

Maybe this will enlighten you:

http://www.techonthenet.com/access/functions/advanced/nz.php

 

[cybertyk]

nope im with u and that 0 at the end is the value it replaces it with if the value is null?

ok right so this is the code all put togeather
Dim strSource As String
strSource = "SELECT tblContact.ContactID, tblContact.[Contact Title], tblContact.[Contact FName], tblContact.[Contact LName], tblContact.[Contact Position]FROM tblContact" & _
"WHERE CustomerID = " & Nz(Me.CustomerID, 0) & "ORDER BY tblContact.[Contact FName], tblContact.[Contact LName];"
Me.cboMail.RowSource = strSource
Me.cboMail = vbNullString

what even to i tag it to? the comboboxes on click or the forms on load?

 

[vbaInet]

That's correct.

It does mention in that link that you put it on the After_Update event of the first combo box. So you would put it on the After_Update event of your Customer's combo box.

Dim strSource As String
strSource = "SELECT ContactID, [Contact Title], [Contact FName], [Contact LName], [Contact Position] FROM tblContact " & _
            "WHERE CustomerID = " & Nz(Me.CustomerID, 0) & "ORDER BY [Contact LName], [Contact FName];"
Me.cboMail.RowSource = strSource
Me.cboMail = vbNullString

I've amended your code. Also, your sorting of FName followed by SName isn't logically. You order by LName followed by FName. The other thing is in a sql statement you don't have to prefix the field name with the table name if you're only using one table.

 

[cybertyk]

ok now im lost, there is no customer combo box,

its just a single combobox that im trying to list the customers contacts on the frount page for the mailmerge buttion.

 

[vbaInet]

If there's no customer combo box then you can put the code in the ON CURRENT and AFTER INSERT events of the FORM.

 

[cybertyk]

ok that creates errors like no tomorrow,


i cant seam to get my head around the record source

so im gonna change it to make it easyer

i want to just display the first name and last name of the current customers contacts

now as the contacts id is linked to the customers autonumberID would this work?

strSource = "SELECT [ContactID], [Contact FName], [Contact LName] FROM tblContact WHERE " & ID.value = Nz(Me.ContactID, 0) & " ORDER BY [Contact LName], Contact FName];"

 

[cybertyk]

the code i just posted produces the folling error

The record source 'false' specified on this form or reort does not exist

the name of the recordsource may be misspelled, the record source was deleted or renamed or the record source exsits in a diffrent database

in the form or report design veiw display the property sheet by clicking the propitys butiion and set the record source propery to a exsisting table

 

[vbaInet]

ok that creates errors like no tomorrow,
so im gonna change it to make it easyer

If there's an error for everytime you move through a record it will display the same error message, that's why it's called On Current. What error does it display? Exact message.

i want to just display the first name and last name of the current customers contacts
strSource = "SELECT [ContactID], [Contact FName], [Contact LName] FROM tblContact WHERE " & ID.value = Nz(Me.ContactID, 0) & " ORDER BY [Contact LName], Contact FName];"

I think that was what you described initially which was made us provide you with a link on how to achieve that. So no that wouldn't work but it's syntactically incorrect. Follow what was initially given.

 

[vbaInet]

Let's see your exact code including the event name.

 

[cybertyk]

Private Sub Form_AfterInsert()
Dim strSource As String
strSource = "SELECT [ContactID], [Contact FName], [Contact LName] FROM tblContact WHERE " & me.ID.Text = Nz([ContactID], 0) & " ORDER BY [Contact LName], Contact FName];"
MsgBox (strSource)
Me.Combo137.RowSource = strSource
Me.Combo137 = vbNullString
End Sub
Private Sub Form_Current()
Dim strSource As String
strSource = "SELECT [ContactID], [Contact FName], [Contact LName] FROM tblContact WHERE " & Me.ID.Value = Nz([ContactID], 0) & " ORDER BY [Contact LName], Contact FName];"
MsgBox (strSource)
Me.Combo137.RowSource = strSource
Me.Combo137 = vbNullString
End Sub

im totaly lost i added the msgbox to veiw the SQL ad it just says false

 

[vbaInet]

You've gone and changed it again.
This:

strSource = "SELECT [ContactID], [Contact FName], [Contact LName] FROM  tblContact WHERE " & me.ID.Text = Nz([ContactID], 0) & " ORDER  BY [Contact LName], Contact FName];"

Should be this (as I wrote it):

strSource = "SELECT [ContactID], [Contact FName], [Contact LName] FROM  tblContact WHERE [B][COLOR=Red]CustomerID[/COLOR][/B] = " & Nz([ContactID], 0) & " ORDER  BY [Contact LName], Contact FName];"

Don't change anything just replace CustomerID with the name of your Customer Id field's name as it appears in the tblContact table. If it's ID replace CustomerID with ID, don't change anything else.