Replies

Sorry. But no, the knowledge base wasn’t of any help. I found a solution though. Instead of the creating the query within MS Access project I just created a view of base on the same select statement on the server. This seems to work. Thanks anyway.

I have created an Access project database that is using records stored on the SQL server. On one of the tables, there is more than 300000 records. But when I tried to see the table from Access project, I can only see 5000 records.

Tried this and tried that. It finally works. Have to make sure the bindingsource fot the lookup table is included, set the datasource, binding source…bla.bla.bla. But it works

umm..i am not sure. but i have a dataset which based on a table on the sql server. the lookup table is also on the server.

I am trying to export data into an xml document following a predefined schema. I’ using sql2000.

oops. sorry. it is a typo. and yes, i need to follow the schema created by an outside party. I am a newbie in XML, SQL/XML as well .NET. Can you possibly point me to the correct way of aproaching this task. I don’t know if I even know where to start! Not all of the fields in my table are on the schema. And of the fields that are on the schema, some are are defined as element, others as attributes etc. Help!

Thank you! Thank you! Thank you. I am forever indebted!

Hello hans, francois
I am attaching a barebone version of the databse I am working on. I kinda able get the counter and part of the concatenation done but I am not sure how to iterate so I can get the punctuation correct. Please help!

I hope this help Hans.
For each symptom I have a checkbox (chkSymptom1 through chkSymptom11). Each corresponding texbox that my user user will be filling in on as needed basis is named txtSymptom1 through txtSymptom11.

Francois
How do I implement your suggestion?

Yes, there is a textbox for each checkbox and no, the users doesn’t always have to fill in each textbox.

Thank Hans. This is exactly what I was looking for.

yep. it is not very efficient and slower too. i wish there is a better way of doing this but can’t seem to find any on the net. even microsoft homepage don’t offer much about a solution to this one. but thanks for your help hans. you don’t know how how time i spent trying to get this to work. i would not know even where to start without your pointer!

I changed my code to look like this and this seems to work so far:

Dim varItem As Variant
Dim strSQL1 As String, strSQL2 As String, strSQL3 As String
Dim ndx As Integer

DoCmd.SetWarnings False

For ndx = 0 To Me!GroupAttendance2.ListCount – 1
strSQL1 = “INSERT INTO tblTemp ( clientid ) ”
strSQL1 = strSQL1 & “SELECT dsdtcmas.clientid ”
strSQL1 = strSQL1 & “FROM dsdtcmas ”
strSQL1 = strSQL1 & “WHERE (dsdtcmas.clientid) = ‘” & Me!GroupAttendance2.ItemData(ndx) & “‘”
‘MsgBox (strSQL1)
DoCmd.RunSQL strSQL1
Next ndx

strSQL2 = “INSERT INTO tblClientNotes ( act, servdate, Notes, NoteDate, Clinician, SessionStart, SessionEnd, clientid ) ”
strSQL2 = strSQL2 & “SELECT tblGroupNotes.act, tblGroupNotes.servdate, tblGroupNotes.Notes, tblGroupNotes.NoteDate, tblGroupNotes.Clinician, tblGroupNotes.SessionStart, tblGroupNotes.SessionEnd, tblTemp.clientid ”
strSQL2 = strSQL2 & “FROM tblGroupNotes, tblTemp ”
strSQL2 = strSQL2 & “WHERE (tblGroupNotes.NoteID)=[Forms]![frmGroupNotes]![NoteID];”
‘MsgBox (strSQL2)
DoCmd.RunSQL strSQL2

strSQL3 = “DELETE tblTemp.clientid FROM tblTemp;”
DoCmd.RunSQL strSQL3

DoCmd.SetWarnings True

End Sub

Hello hans,
i tried your code. however, when there is more than 25 items in the listbox I still get the SQL too complex error message. is it normal for Access to behave like this?

i forgot to mention that the original table that i am copying the data from (dsdtcmas) is an old foxpro dbf type file, may be this has something to do with it.

Hello Hans,

The row source type for lstAttendees is Table/Query

The row source is:

SELECT dsdtcmas.clientid, Trim([lastname]) & “, ” & Trim([firstName]) AS ClientName
FROM dsdtcmas INNER JOIN dsdtchgp ON dsdtcmas.clientid = dsdtchgp.clientid
WHERE (((dsdtchgp.meeting)=[Forms]![frmScheduledMeetings]![lstMeetings]) AND ((dsdtchgp.MeetingDate)=[Forms]![frmScheduledMeetings]![lstMeetingDate]))
ORDER BY Trim([lastname]) & “, ” & Trim([firstName]);

I was trying to run the append query from the query design grid with both frmScheduledMeetings and frmGroupNotes open. The append query works really well when there is only one person is listed as attending the meeting on the specified date in lstAttendees. When there are more than one attendees, the error message that I get is