martinrobson
Programmer
Hi,
I have a listbox which is used as the first form in my vba application to display all the records that have been entered. I would like to build in some functionality which allows me to edit a specific record, so would like to be able to diplay a specific record in a new form.
For some reason when i select an item from the listbox and click the appropriate button, it will not display the specific record i have chosen, but will try and diplay them all, starting with the first record in the listbox.
The code i am using to do this is the following, i am using access 97 by the way:
Private Sub Ammend_Button_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim CampaignCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCampaignSelect"
' Loop through the selected items in the list box and build a text string
If Me!CAMPAIGN_LIST.ItemsSelected.Count > 0 Then
For Each varItem In Me!CAMPAIGN_LIST.ItemsSelected
CampaignCriteria = CampaignCriteria "tblData.Region = " & Chr(34) _
& Me!CAMPAIGN_LIST.ItemData(varItem) & Chr(34) & "OR "
Next varItem
CampaignCriteria = Left(CampaignCriteria, Len(CampaignCriteria) - 8)
Else
MsgBox "Please select a campaign to ammend"
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM CAMPAIGN " & _
"WHERE " & CampaignCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryCampaignSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
Iventually i would like it to return the data from two tables onto a form but i thought that i should get one working first. The table i am trying to get data from is the CAMPAIGN table.
The listbox itself has 8 columns in it containing a unique ids and other data.
I have found that changing the select statement to just
str = "SELECT * FROM CAMPAIGN " returns exactly the same set of data in the query as if i was using the string, so i am assuming that for some reason the string is not getting populated with my selection.
Any help would be much appreciated, this did work but since adding more columns to the table it has gone belly up.
thanks in advance
Martin
I have a listbox which is used as the first form in my vba application to display all the records that have been entered. I would like to build in some functionality which allows me to edit a specific record, so would like to be able to diplay a specific record in a new form.
For some reason when i select an item from the listbox and click the appropriate button, it will not display the specific record i have chosen, but will try and diplay them all, starting with the first record in the listbox.
The code i am using to do this is the following, i am using access 97 by the way:
Private Sub Ammend_Button_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim CampaignCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryCampaignSelect"
' Loop through the selected items in the list box and build a text string
If Me!CAMPAIGN_LIST.ItemsSelected.Count > 0 Then
For Each varItem In Me!CAMPAIGN_LIST.ItemsSelected
CampaignCriteria = CampaignCriteria "tblData.Region = " & Chr(34) _
& Me!CAMPAIGN_LIST.ItemData(varItem) & Chr(34) & "OR "
Next varItem
CampaignCriteria = Left(CampaignCriteria, Len(CampaignCriteria) - 8)
Else
MsgBox "Please select a campaign to ammend"
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM CAMPAIGN " & _
"WHERE " & CampaignCriteria & ";"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryCampaignSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub
Iventually i would like it to return the data from two tables onto a form but i thought that i should get one working first. The table i am trying to get data from is the CAMPAIGN table.
The listbox itself has 8 columns in it containing a unique ids and other data.
I have found that changing the select statement to just
str = "SELECT * FROM CAMPAIGN " returns exactly the same set of data in the query as if i was using the string, so i am assuming that for some reason the string is not getting populated with my selection.
Any help would be much appreciated, this did work but since adding more columns to the table it has gone belly up.
thanks in advance
Martin
