4 Combos, 1 Form, 2 SQL Server Queries, 1 Access Query

[hegartyjp]

Hello.

I am having an issue that is driving me insane today.

I have an access form on which there are 4 Combo Boxes.

The form is in MS access but the data to populate the combos is on SQL Server.


I have a pass through query that links to the server and when the form opens, the following code runs:



Private Function GetLevel1()

Dim q As QueryDef

Me.TxtLevel1.ListRows = 20
Set q = CurrentDb.QueryDefs("pt_qryRequest")
q.SQL = "exec dbo.sp_comboTeamNamesLev1 "
Me.TxtLevel1.Requery


End Function

Private Function GetLevel2()

Dim q As QueryDef

Me.TxtLevel2.ListRows = 20
Set q = CurrentDb.QueryDefs("pt_qryRequest")
q.SQL = "exec dbo.sp_comboTeamNamesLev2 "
Me.TxtLevel2.Requery

End Function



But once the form opens, the same data is listed in both of these combo boxes.

I have looked at another database here and it does the same thing - Codewise for 2 seperate combos but when the form open there is diffreent data in both combos. If it was not for seeing this I would have given up by now and figured it can't be achieved with one pass through query
Am I doing something completely stupid or is there something really clever in the other database.

I can not see where there is a differenece.

I would really appreciate any comments on what I am doing wrong.

Thank you in advance

JP

 

[dhookom]

It looks like both combo boxes have the same Row Source yet you expect unique values in each. Why not just use two pass-throughs or change the Row Source Type to value list and build the values from the query?

Duane
Hook'D on Access
MS Access MVP

 

[hegartyjp]

Hi,

Yes both combos have the same row source.

The thing is I have another form to do later that will have about 12 combos

So I can not create a seperate pass through query each time.

And there are values to be assigned to the combos for what has already been selected so they can not be bound.

Thanks for reply

 

[NXMold]

I think we are missing some information, what is the row source of the combo box? How is the function called?

I expect something like:

cbo1.rowsource = GetLevel1

but this requires another line in the function, as right now it looks like it dosen't return anything?

GetLevel1 = q

Or should the function have a line:

Me.TxtLevel2.Rowsource = q

 

[hegartyjp]

Hi,

Well on the actual combobox on the form

The Rowsource is: pt_qryRequest

This is the name of the pass through query to pull info from SQL Server.

So it sets q to be the Quereydef for this and then sets that to the required stored procedure.

I have another form wher it works with just one query in what appears to me to be exactly the same.


The code is called when the form opens

GetLevel1
GetLevel2

I really hope you can help. It is driving me mad here !!!!

 

[hegartyjp]

On the other form these functions are called when the form opens and each combo has different values.....I just dont get it !!!!!!


Private Function GetBranches()

Dim q As QueryDef
Me.fldBranchCode.ListRows = 10
Set q = CurrentDb.QueryDefs("pt_qryRequest")
q.SQL = "exec dbo.sp_comboBranchesbyDescription "
Me.fldBranchCode.Requery

End Function



Private Function GetConnection()

Dim q As QueryDef
Me.fldConnection.ListRows = 10
Set q = CurrentDb.QueryDefs("pt_qryRequest")
q.SQL = "exec dbo.sp_comboConnection "
Me.fldConnection.Requery

End Function



Private Function GetRM()

Dim q As QueryDef
Me.fldRMReference.ListRows = 10
Set q = CurrentDb.QueryDefs("pt_qryRequest")
q.SQL = "exec dbo.sp_comboRelationshipManager "
Me.fldRMReference.Requery

End Function



Private Function GetRegion()

Dim q As QueryDef
Me.fldRegion.ListRows = 10
Set q = CurrentDb.QueryDefs("pt_qryRequest")
q.SQL = "exec dbo.sp_comboRegion "
Me.fldRegion.Requery

End Function

 

[dhookom]

So create 14 pass-throughs or use code to set the Row Source to a value list. Don't expect the same query to pull two different recordsets.

If your Row Sources are similar, could you add a field to your pass-through so each combo box could have a Row Source like:

SELECT FieldA, FieldB FROM pt_qryRequest WHERE FieldC = 'Level1';

Duane
Hook'D on Access
MS Access MVP

 

[hegartyjp]

Yeah if I really have to, I will create more but I would really prefer not to and the fact that i can see it working the correct way using the above code is driving me mad.

I just can not see how it works on one form but does not work for me

Thanks for suggestions though

 

[dhookom]

I expect there may be something that is requerying your entire form. This would cause all controls to requery. You might also try change the code from the On Open to the On Load event.

Duane
Hook'D on Access
MS Access MVP

 

[hegartyjp]

Changed to On Load but no different.

You may be on to something with the refresh.

I will try and find if there is anything causing the form to requery

Thanks

 

[hegartyjp]

I can not find anything that would appear to be causing the full form to refresh. I just can not see how one database is working perfectly and the other is rubbish........

Do you have any idea if there are any settings or anything that would cause this refresh.

It can not be a PC issue as I have opened both the working and non working form on the same PC

 

[dhookom]

I don't have any idea what could be causing the issue. I would stop trying to figure it out and just use one of the alternatives that have been suggested. I would have never attempted to use the same Row Source on more than one combo box and expected to display different results.

Duane
Hook'D on Access
MS Access MVP

 

[hegartyjp]

Cheers Fella.

I am way too stubborn for that. There is another person that works here that created the other form but is on holidays. I can not have her come back and see I used 14 queries to do what she done in one lol......

But Thank you for all your efforts.

Much Appreciated

 

[dhookom]

I would write a single, small function that would populate the Row Source from the pass-through. This would require changing the Row Source Type to Value List. Then just loop through your combo boxes and call the function.

Duane
Hook'D on Access
MS Access MVP

 

[hegartyjp]

Yeah Fella. But on the next form there are going to be combos with 100s of records in them.....

Possibly thousands

 

[dhookom]

IMHO, I don't think it is good practice to expect the same query to return more than one recordset.

If you insist on pursuing, I would set a breakpoint in the code to see when the row source changes.

Duane
Hook'D on Access
MS Access MVP

 

[NXMold]

Wait, I think I see what is happening in the "old" code. There is a function for each of (4) different combo boxes, it looks like the function rewrites the one pass-thru query based on some control value, requeries the combo box, and repeats. I'm still not sure of the whole picture, and I'm NOT an expert! Perhaps you can debug the old form, step thru each line one-by-one (F8) and watch what is happening. Or try

Sub Form_Open

    Dim q As QueryDef
    Set q = CurrentDb.QueryDefs("pt_qryRequest")

    q.SQL = "exec dbo.sp_comboTeamNamesLev1 "
    Me.TxtLevel1.Requery

    q.SQL = "exec dbo.sp_comboTeamNamesLev2 "
    Me.TxtLevel2.Requery

End Sub

 

[hegartyjp]

Thanks Fella,

But it makes no differeence if I put them in one function or 2.

I am going to have a long day i feel lol......

 

[Axworthy]

Try changing the requery to a rowsource.

instead of:
Me.TxtLevel1.Requery

try this:
Me.TxtLevel1.RowSource = "pt_qryRequest"