sql query arrays

[vbaInet]

It seems with the new version of Access the DAO object library is now included in the Microsoft Office 14.0 Access Database Engine library, so you don't actually need to tick the DAO library.

 

[drewdrewdrew]

yeah I saw that.... I have pm'ed you the link and brief flow of how it works.

 

[vbaInet]

You said that your question_id field is an Integer, it's a Text field. A bad design saving numbers as text. And it seems you have other number fields that you're saving as text as well.

It's either you change the field to Number (in which case you have to do a complete overhaul of your system like changing the corresponding Foreign Keys to Number and sorting out every piece of code that uses this field type incorrectly) or you change the FindFirst method to this:

.FindFirst "[question_id] = [COLOR="red"]'[/COLOR]" & Me.question_list & "[COLOR="Red"]'[/COLOR]"

If you're going to do it this way, then you can remove the CLng() cast when you're adding the items to the listbox.

 

[drewdrewdrew]

apologies for the lack of finesse with the whole setup, I approached it wrong in where I was looking at fine tuning these little niggly field type things at the end vice doing on initial plan which was rushed. It is not a majorly complex solution required.

I have updated all the number fields, that ARE numeric accordingly and the AfterUpdate event runs without quarms.

where was it going from there?

 

[vbaInet]

Because you're using these fields in code there are certain rules to be followed when handling type String, Int and Date assignments. As you noticed above for Strings you have to wrap the value in Single Quotes. Just be sure you've overhauled every piece of code.

So how's the code looking at the moment? And what's the result?

 

[drewdrewdrew]

So am I passing the full array from the first form as a string ? eg "3,4,5,6,7,8"

After the string is dissected with

strArray = Split(strArrayValue, ",")

convert to integer?

or pass the whole array as integer?

 

[vbaInet]

If you were passing an array in the first place then you can control what data type it should be but you're passing a string and when split it's also split into a string array (this behaviour can't be changed unless you use some other technique). So convert to Integer when you're saving to the listbox.

 

[drewdrewdrew]

Typename : Long

 

[vbaInet]

Typename : Long

What does this mean?

 

[drewdrewdrew]

Private Sub question_list_AfterUpdate()
Dim rst As DAO.Recordset
' Get a copy of the form's record source
Set rst = Me.RecordsetClone

With rst
' Find the record
.FindFirst "[question_id] = " & Me.question_list & ""

' If there's a match set the bookmark of the form to that of the one we've just searched for
If .NoMatch = False Then
Me.Bookmark = .Bookmark
End If
End With
MsgBox "Listbox value is: " & Me.question_list.Value & vbNewLine & "Type Name: " & TypeName(CLng(Me.question_list.Value))
' Clean up
Set rst = Nothing
End Sub

from this ... where were you moving to once this was working without error?

 

[vbaInet]

You'll use the same code to in your buttons, move the item selected in your listbox (back or forward), get the value, use that value in the code to search for the record and the form's current record becomes the found record.

 

[drewdrewdrew]

what code would be in the btn_next_OnClick() command be.. I am understanding how to lock on the current question_id from the array... I still cant visualise the "next" button..... in my mind I thought it would be something like .next or +1... but obviously well above me skill wise

 

[vbaInet]

To an extent it will be Current Record + 1 and since the code is in the listbox's event you'll be using the listbox as your pivot. So with the Next button you'll set the listbox's value to the next item and the event should fire. If it doesn't fire, you simply call it to fire.

 

[drewdrewdrew]

on a side note.. is it better to pass all the variables to global variable and change them when needed vice putting all the values into hidden textboxes?

or is there another way to handle variable/values passed from one form to another?

cheers

 

[vbaInet]

It depends on the circumstance. Are you passing values from a control to another form? Or are you passing values from a variable to another form? Also remember, if the variable's value is derived from a control then you're initially passing the value from the control.

 

[drewdrewdrew]

all the information passed is from initial SQL queries based on a user id.

so information that I currently retain in the textboxes (which are session based) are:

question_id (current question id)
question_type (type of question)
testcard_id (testcard linked to question)
array_list (array list of question ids for that testcard)

I do have declared globals as follows:

userid
fullname
employeeid

Just not sure... it just seems messy to have a dozen hidden text boxes to hold information about that session.

Or is that a normal thing.?

 

[namliam]

I only skimmed the thread, appology if it has been mentioned before

Dim i, j, k As Integer

If you declare like this, i and j are not Integers, instead they are variants.
Proper way to do it

Dim i As Integer, j As Integer, k As Integer

 

[vbaInet]

@namliam: No Mailman, I didn't spot that so thanks for mentioning it

@drew: This is where Classes come into play or the less complicated and less flexible way of doing it called Type or even another called Dictionary. I can't really go into details, it's a broad topic.

Anyway, to keep things simple just use global variables.

 

[drewdrewdrew]

slowly slowly piecing this together.... from the below list how would I select the record that a user selected..? right now it lists all the questions I want in the array however when a question is selected and the person clicks a command button associated with it... the combobox just defaults to empty and I have to select another question to make it visible.

 Dim db4 As DAO.Database
Set db4 = CurrentDb
Dim rs4 As DAO.Recordset
Dim i As Integer, j As Integer, k As Integer
Dim strArrayValue As Variant
Dim strArray() As String
 strArrayValue = Me.q_array_list.Value
strArray = Split(strArrayValue, ",")
 
For j = 1 To Me.question_list.ListCount
    Me.question_list.RemoveItem 0
Next j
 
For i = LBound(strArray) To UBound(strArray)
Set rs4 = db4.OpenRecordset("SELECT q_question_list.question_id, q_question_list.config_id FROM q_question_list WHERE (((q_question_list.question_id) In (" & strArray(i) & ")));")
    
    With Me.question_list
        .AddItem Item:=CLng(rs4!question_id) & ";" & rs4!config_ID
    End With
 Next i
 rs4.Close 'Close the recordset
Set rs4 = Nothing 'Clean up
End If

the command button that loads the above question

 Private Sub btnLoadQuestion_Click()
Dim str1 As Integer
Dim str2 As Integer
Dim str3 As String
'Dim str4 As Integer
 str1 = Me.question_list.Value
str2 = Me.tc_select.Value
str3 = Me.q_array_list.Value
'str4 = Me.tc_select.Value
 DoCmd.Close
DoCmd.OpenForm "frm_question_interface_tmp", , , , , , str1 & "|" & str2 & "|" & str3 ' & "|" & str4
End Sub

 

[drewdrewdrew]

thanks vbaInet... I will progress with that recommendation and also look into the dictionary option.

just cant get the above combobox to stay on the selected question. Am I to believe my next button relies on the combobox box having the question selected to provide a reference point to go + or - a record.