[RESOLVED] help with sql query !

[CM670]

on my form i have a 'yes' button. this form has an id num on it. When the user clicks the yes button another form opens with a new id number. say the id number on the fiorst form is 1 and the id on the second form is 2.

I then do an insert into my linked so it stores

Prev_id          new_id
1                        2

Then the user can press the yes button on the second form and open a 3rd form and open anther form with a new id so the table now looks like this:

Prev_id          new_id
1                        2
2                        3

This can go on as many times as the user clicks on the new forms opened so the table could end up looking like this:

Prev_id          new_id
1                        2
2                        3
3                        4
4                        5
5                        6

i need to write some code which gets the original form id which would be 1..however i cant get my head aroud how i need to do it


so far i have done

PrevSQL="SELECT Prev_id from linked where id='" & txtId.Text & '"
'i then open the recordset and assign the variable prevTest to the value returned in the rs.

I know that i then need to
select prev_id from previous where id=prev test

and i think i need a loop somewhere... can anyone help me with this please!!

Thanks!

[SamOscarBrown]

In a module, create a program-wide public variable, called, say, "origFormID"
Then when the button on the first form is clicked, set that variable to 1. If you need one for EACH form, you could declare a public array, and set each element of that array with each forms 'id'.

Yes, you could always store that in your DB, but setting a Public variable would be quicker.

[ColinE66]

Well, assuming that you are the only user writing to this table the answer is SELECT MIN(Prev_id) FROM linked.

I suspect, however, that your illustration of the problem is lacking some relevant information.

If for example, many users are accessing your 'linked' table then you would need a new column (for userid). If, beyond that, the minimum prev_ID value is not always 1 then you need something like:

select min(a.prev_ID) from linked a, linked b where a.next_ID = b.prev_ID

Hope that helps...

[DataMiser]

I am wondering why you would be opening a new form everytime rather than simply using the same one. I assume the info collected would be the same so having a bunch of forms opening up would just complicate things from both the users pov and from a coding pov.

What is the reason for the new forms?

[CM670]

Thanks for all the replies!

DataMiser it is just for a project i have to do and it has to be done in that way..

I went with the suggestion from SamOscarBrown and used a program wide public variable as this seemed the easiest for me!

Thanks everyone