help - link

[compcad]

i have 2 tables one called prize and the other called raffles. and i am creating a program in vb6 using these tables from ms access 2000.

the person i am building this vb program for wants these 2 tables to be linked. the problem is none of the feilds in either tables match for linking purposes. he does not want to add another feild to either table.

is what i had thought of was taking the feild from the raffle table which is prizeid and setup a textbox to access the data from this feild. i had created a sql statement for this table. next i create a copy to the clipboard by:

clipboard.clear
clipboard.gettext text3

next i took another textbox text4 and add:

text4=clipboard.settext

then i took text4 and stuck it into another sql statement as such:

sql="select prize1, value, gift from prize where prize1 like '" & text4 & "'"

to find the like value between the two feilds.
everything works fine up to:

text4=clipboard.settext

this point.

could you give me some ideas of what i could try that i am missing if any come to mind.

thank you
stephen mentzer

 

[vanvb]

I'm confused about a couple of things.
1) Why are you using the clipboard? Why not just set text4.text = text3.text?

2) Why use the text4 field at all? It seems like you would be just as well using text3 in your sql statement because you are just putting its value into text4.

3) One other thought...what data type is the prizeid field in the raffle table and the prize1 field in the prize table? Are they identical values and date types?

 

[compcad]

1) that idea had slipped my mind i need to try it

2) if i just used text3 how would i connect the two feilds to this one textbox
a) the raffle sql statement is already using the like command for one of the other feilds

3) i am not sure until i get home

thank you
stephen mentzer

 

[vanvb]

> if i just used text3 how would i connect the two feilds to this one textbox

I guess I don't know what you are trying to do when you connect them. Let me guess. You have a table of raffle tickets. You have a table of prizes. Raffle tickets can win one(or more?) prize. You want to show what prize was won by that raffle ticket - is that the link you are referring to?

Are you trying to track the prizes won by each raffle ticket? If so, maybe look at adding a 3rd table. It would have an autonumber type ID field, a PrizeID field, and a Raffle Ticket Number field. You would add an entry every time a prize is won by a raffle ticket number.

Let me know if this is what you are trying to accomplish and if this gets you anywhere. If this is off base, let me know what you are trying to display from each table and I can maybe help more then.

 

[compcad]

is what i am trying to do is develop a program that will print raffle tickets that i designed. the program is almost completed exccept for connecting the prize table to the raffle table.

the prize table has data needed that needs to be on the tickets such as:

prize value: example($64,000.00)

prize name: example (red mercedes convertible)

depending on the raffle the prizes will change. that is why i need to connect from the raffle table and the prize table these two feilds:

prizeid and prize1

the way i talked about earlier in the day.

thank you
stephen mentzer

 

[vanvb]

Then it looks like you are on the right track with that sql statement...just needed to forget about the clipboard thing. Also make sure you are comparing the right kinds of data types.

Good luck!