converting information from table to report (a2k)

Topic

New Reply

Ok, here it goes. I am trying to develope a report that pulls out my states from the table. Now earlier I posted code that shows how to extract them and put each state into numbers. Now I need to convert the numbers into the lettered states. Should I set up an array and then some kind of loop to do this or is there a different way? Just in case here is the code that extracts the numbers from my form and puts it into a field for storage.

Private Sub Form_Current()
Dim intMyNum, arrMyList
For i = 0 To (lstStates.ListCount – 1)
lstStates.Selected(i) = False
Next i
If txtStates “” Then
arrMyList = Split(txtStates, “*”)
For j = 0 To UBound(arrMyList)
If IsNumeric(arrMyList(j)) Then
intMyNum = CInt(arrMyList(j))
lstStates.Selected(intMyNum) = True
End If
Next j
End If

End Sub

Private Sub lstStates_Click()
txtStates = Null
For i = 0 To (lstStates.ItemsSelected.Count – 1)
txtStates = txtStates & lstStates.ItemsSelected.Item(i) & “*”
Next i
If Len(txtStates) > 0 Then
txtStates = Left(txtStates, (Len(txtStates) – 1))
End If
End Sub

Reply | Quote

Replies

Maybe I am not understanding this properly, but if it is sometimes necessary to use a number for a particular state and at other times the letters for that same state, why not use a table that has both these fields per state. Then you could easily get either notation for a state with a link to this table.

If I’m simplifying this too much, I missed the point, but it seems to dismiss the inherent advantages of a relational db to do this all in code.

Reply | Quote

Well see I started out doing it like that but I was having problems with a few things. Let me clarify kind of what led me to do what I did.

I had to create a data base that stored a bunch of different information. Now the user has to be able to select a multiple number of states for each individual record being entered. The record that is being entered could have a possibility of 56 things to be chosen. They are allowed to chose more than one since it is where the person is interested in going. I had problems with creating a table that had a State ID and then the list of options, a table with all relevant user info, and then a table between with UserID and State ID to create a one -to – many relationship. I was having problems with the recalling of records showing the states that a person would chose. So I developed code that would extract what was clicked on and save it into a text box and then put it into the relevant field in the Users table. Now I am trying to figure out how I can extract that data and reconvert it back into the state names. I hope this helps at all.

Reply | Quote

What Thomas was talking about is a lookup table. It doesn’t have to have numbers as the primary key in it, you could use state abbreviations if you wished. Then you would only need to lookup the state name associated with that state abbreviation. Isn’t that what you’re trying to do?

Reply | Quote

In a sense yes. The problem I ran into earlier was that I couldn’t find a way to store the states a given record would have so that when I recalled the record it would show what states that person chose. I had asked if there was an easier way to do it earlier but didn’t get a whole lot of help, thus causing me to develop what I did so that it would work. Of course I didn’t think about the reports. So if I can find out how to just convert those numbers somehow, or if i can figure out an alternative way to do it so the record remembers what states were chosen, and then put it all in a report that would be great. I am not exactly a veteran with access, I am rather new. Thanks for the help

Reply | Quote

In Post 143213 I showed you how to store the chosen states in an intermediate table. That approach would have made reporting much easier.

But, anyway. You can do this in the OnFormat event of the detail section of a report. The code will look a bit like the code you have in the OnCurrent event of the form. Instead of setting the Selected(n) property of a listbox, you add the state name to a string.

I assume you already have a table with the state ID’s (numbers) and names or abbreviations. In the example below, I have used tblStates as table name, StateID as id field and StateName as descriptive field.

As mentioned, the code is meant for the OnFormat event of the Detail section of a report.
Put a text box in the Detail section bound to the States (?) field. Name it txtStates as on the form, and make it invisible. It is used to fill a text box txtStateList with its text equivalent.

Dim intMyNum, arrMyList
Dim strStates As String
strStates = “”
If txtStates “” Then
arrMyList = Split(txtStates, “*”)
For j = 0 To UBound(arrMyList)
If IsNumeric(arrMyList(j)) Then
intMyNum = CInt(arrMyList(j))
strStates = strStates & “; ” & Dlookup(“StateName”, “tblStates”, “StateID=” & intMyNum)
End If
Next j
End If
‘Get rid of first “; ”
If strStates “” Then
strStates = Mid(strStates, 3)
End If
txtStateList = strStates

Reply | Quote

Thanks for the help, I actually figured it out about 20 minutes ago. I used a similar method as you hans, just did a small design change with what I was doing. her is my code:

Private Sub Form_Current()
Dim strSearch, strOther
For i = 0 To (lstStates.ListCount – 1)
lstStates.Selected(i) = False
Next i
If txtStates “” Then
strOther = Split(txtStates, “, “)
For j = 0 To UBound(strOther)
For k = 0 To (lstStates.ListCount – 1)
If strOther(j) = lstStates.ItemData(k) Then
lstStates.Selected(k) = True
End If
Next k
Next j
End If

End Sub

Private Sub lstStates_Click()
txtStates = Null
For i = 0 To (lstStates.ItemsSelected.Count – 1)
txtStates = txtStates & lstStates.ItemData(lstStates.ItemsSelected.Item(i)) & “, ”
Next i
txtStates = Left(txtStates, (Len(txtStates) – 2))
End Sub

Again thanks for the patience and help.

Reply | Quote