i posted this twice already in the tables section but i guess that here is more appropriate for this kind of stuff


ok, thanks to all of you that helped me out until here, it wouldn't have been possible without you (could it?)

but iv'e now run into another problem: iv'e made some nice solution to my original problem (which i will post below): i changed to code accordingly and set the value of the text box to an integer, so there shouldn't be any data type mismatch so i don't get any errors and the combo list actually works half-way, the problem is that i'm prompted to enter a value into another window - why can't the combo box recognize the value that i typed in already?

here's the code:

Private Sub box1_AfterUpdate()
Dim ono As Integer
ono = box1

On Error Resume Next
co1.RowSource = "Select quotes.qID FROM quotes WHERE quotes.oID = ono ORDER BY quotes.qID;"
End Sub

(ok, mind the on error resume next - it's not my original code, and i forgot to remove it)


original problem


how can i make a combo-box display ony values of a certain field that correspond only to that specific record:

for example, i have a table called "quotes" where the quotes are stored. there can be multiple quotes for the same order request - i.e. the "order" form (table) - they are linked together via the order number using a one-to-many relationship. the 'quotes' table is a subform on the 'order' form

but i wanted to add another field in the order form: i want a drop-down menu where the user can select which of the quotes was the final quote (that was actually ordered and processed). now i don't know how to do this: if i set the quotes table as the source then i will get the whole list of quoted from the whole database each time i open the menu, all i want to see is the few quotes that are related to that order no. (both the quotes and orders have seperate numbers: oID and qID)

....

here's the link to the whole thing
http://www.utteraccess.com/forums/showflat...;o=&fpart=1

thanks for looking

- mordy

Try this

co1.RowSource = "Select quotes.qID FROM quotes WHERE quotes.oID = " & ono & " ORDER BY quotes.qID;"

thanks. can you explain the logic behind that
- why do i have to put in in quotes? ono is a local variable and isn't by any means a string of any kind, and what do those "&" do?
(it worked)

thanks a million

What is more appropiate would be that you post your problem only once .. and only once in that forum ...
Cross Posting and Double Posting is not allowed here at Utter Access.

Please refrain from this practice in the future.

Ricky Hicks
Utter Access Forum Adimin

You’re right (local variable) but ono is not in quotes. The first half of your SQL statement is "Select quotes.qID FROM quotes WHERE quotes.oID = " and the last half is in quotes " ORDER BY quotes.qID;". The local variable is being tied into the SQL by the &. When you have an SQL statement and you want to add a variable you don't put it in quotations, for example:

Dim strSQL as String
Dim i as Integer

i = 10

strSQL = "SELECT FileID"
strSQL = strSQL & " FROM tblName"
strSQL = strSQL & " WHERE [FileID] = " & i & ";"

or string in SQL statement

Dim strSQL as String
Dim strName as Interger

strName = "Jones"

strSQL = "SELECT fldPerson"
strSQL = strSQL & " FROM tblName"
strSQL = strSQL & " WHERE [fldPerson] = ' " & strName & " ' ;"

I hope this helps.

unfortunately, i didn't really understand what you said. but i think it's my problem

what i have figured out from your replies is that when i want to mix VBA and SQL then i'll have to use a different and very weird syntax, which is totally different from SQL and VBA alone.

i guess i just have to learn more. (it would be helpful if someone can provide a link with a table for VBA-SQL linking syntax)

and btw, i only double posted it after seeing that it bumped back to page 2 - so i figured that most people wouldn't read it anymore. is that ok? i don't want to annoy people or break the rules (it's enough i have people answering my questions at their own good will)

All you have to do is make a new reply to your original post and the post to bump it back to the top ...
So there is no need make a new post on the same problem.

It only confuses the other members here and scatters your problem within the forums ...

Ricky Hicks
Utter Access Forum Admin

ahhh.... ok. what's about the conclusion i came to about SQL-VBA?