Combo box - not in list, 2 different tables?

[MikiH]

Hi all.

I've got a list of suppliers and a related table for the projects each supplier works on i.e.

Supplier 1
project1
project2
Supplier 2
project1
project3


On a form I've got 2 combo box & other controls. The first combo box you select the project and the second you select the supplier, this being filterd only to show suppliers that work on that project.

The problem I've get is with "not in list", how do I determine if the entry is either

1) A new supplier (ie supplier 3) and open the suppliers form on a new record

or

2) A exsisting supplier, but doesn't have the project listed for them (ie Supplier 2, project 2) and open the suppliers form and move to the correct supplier (supplier 2 in the example)


I can open the forms and move to the correct location but how do find out which case is correct?


Thanks
Mick

 

[PHV]

To know if it's a new supplier you may consider the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikiH]

Haven't used Dlookup before have you got an example?

Thanks

 

[PHV]

Try to use the F1 key when the cursor is in the DLookUp word.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BSman]

When the user has selected the project, for the afterupdate property of that combo box run something like the following code which is intended to determine if any supplier is currently associated with that project:

dim lngCount as Long
lngCount = DLookup("SomeColumn","tblSupplierProjects","[ProjectID] = " & me.cboSelectProject)

If lngCount = 0 then
msgbox "There currently is no supplier for this project"
me.cboSelectSupplier.RecordSource = "[tblSuppliers]"
me.cboSelectSupplier.Requery
end if


If the record source for cboSelectSupplier (I'm calling this the second combo box on your form) is the list of suppliers (and you need to permit adding a new supplier), then for the afterupdate property if the user selects to add a new supplier you should bring up the form that you want to use for adding a supplier to the list. Be sure to requery cboSelectSupplier when you return to the primary form so that the new supplier will be displayed.

I know my explanation is a bit choppy, but hopefully it will guide you in the right direction.

 

[MikiH]

Thanks for the points, the code I used was

' check if supplier already in list of companies

Dim compCount As Double
compCount = DCount("[Company Name]", "[TBL Companys]", "[Company Name]= '" & NewData & "'")


' if supplier is not in list ask if want to add new
' supplier - open frm companys - new record
If compCount >= 1 Then

MsgBox "new supplier or spelt incorrect"

' add code to open supplier form at new record



Else
' ask if want to add project to exsisting suppliers
' list - open frm companys - find supplier

MsgBox "exsisting supplier - project not listed"

' add code to open supplier form at exsisting supplier


End If



Thanks

 

[BSman]

Two points regarding your code:

1. compCount should really be an integer, not double. Double and single are used when a number may include decimal values. If the numbers you will be handling will always be whole numbers (integers) then you should define those variables (and columns in tables) as integer or long integer to avoid the problems that occur because binary numbers (as double and single numbers are stored) don't always exactly match decimal numbers. With integers 1 is always 1 (not .999999999, for example), so you know they will match.

2. You have a new supplier when compCount = 0, not when it is >= 1. This is the opposite of the code you posted.

3. I'd suggest letting the user select the supplier from a combo box based on the supplier list but allow the user to type in a supplier not on the list.

 

[MikiH]

Thanks for the reply.

1) Will change it to an interger, I generally use double as when I first started developing dbs couldn't find out where my decimal places were going.

2) Your right, when I tested the code properly I found this as well so I just moved the code around in the if statments.

3) Form is setup with combo boxes. Not quite sure what you mean? Maybe turn off the not in list event and put it in the afterupdate? What are the benifits?

Thanks this good advice for all bedroom techies like myself.


Mick

 

[BSman]

Set the limit to list property to No (False). This allows the user to type in a new value. You might then want to run code with the AfterUpdate property that would check to see if it is a new value and, if it is, add it to the list. Or, if it's a new supplier, pop up a form to add the information about the new supplier (pass the entered name to the form) after you have confirmed that the user really meant to add a new value.

Allowing new values to be entered is particularly useful when you create the combo box from the values in the current table. For example, suppose a column is "color" and you don't want or need to limit the colors to a lookup table list. Build a combo box based on a grouping query of the color column from the current table, but allow new values to be entered. In that situation you will cut down spelling and bad data entry errors while still permitting easy entry of new values. Lookup tables usually work better when you force users to click on a button to add new entries into the lookup table through a separate form.