ms access,how to edit!? please help me..

[Condomx]

Im having a problem in how to edit the record from my ms access database because everytime i click the save button after i am done editing it says "Query is too complex"..what should i do? please help me...!? does anyone knows what code must be done on this please? will please check the code below,its the code of my save button in the edit section:

 connection
 rs.Open "select * from permanent where num='" & num.Caption & "'", con, 3,3
    If rs.RecordCount <> 0 Then
       rs.Update
       rs("num") = (num.Caption)
       rs("name") = UCase(txtname.Text)
       rs("sex") = (sex.Text)
       rs("year") = (year1.Text)
       rs("ncee") = (ncee.Text)
       rs("month") = (month.Text)
       rs("day") = (day.Text)
       rs("year1") = (year2.Text)
        rs("place") = (place.Text)
        rs("city") = (city.Text)
        rs("provincial") = (prov.Text)
        rs("parent") = (parent.Text)
        rs("address") = (address.Text)
        rs("primary") = (prim.Text)
        rs("year2") = (year3.Text)
        rs("intermediate") = (inte.Text)
        rs("year3") = (year4.Text)
        rs("high_school") = (high.Text)
        rs("year4") = (year5.Text)
        rs("year5") = (co1.Text)
        rs("elp1") = (elp1.Text)
        rs("no1") = (no1.Text)
        rs("final1") = (fin1.Text)
        rs("final2") = (fin2.Text)
       rs("final3") = (fin3.Text)
       rs("final4") = (fin4.Text)
       rs("final5") = (fin5.Text)
       rs("final6") = (fin6.Text)
       rs("final7") = (fin7.Text)
       rs("final8") = (fin8.Text)
       rs("re-xam1") = (re1.Text)
       rs("re-xam2") = (re2.Text)
       rs("re-xam3") = (re3.Text)
       rs("re-xam4") = (re4.Text)
       rs("re-xam5") = (re5.Text)
       rs("re-xam6") = (re6.Text)
       rs("re-xam7") = (re7.Text)
       rs("re-xam8") = (re8.Text)
       rs("units1") = (unit1.Text)
       rs("units2") = (unit2.Text)
       rs("units3") = (unit3.Text)
       rs("units4") = (unit4.Text)
       rs("units5") = (unit5.Text)
       rs("units6") = (unit6.Text)
       rs("units7") = (unit7.Text)
       rs("units8") = (unit8.Text)
       rs("remark1") = (rem1.Text)
       rs("remark2") = (rem2.Text)
       rs("remark3") = (rem3.Text)
       rs("remark4") = (rem4.Text)
       rs("remark5") = (rem5.Text)
       rs("remark6") = (rem6.Text)
       rs("remark7") = (rem7.Text)
       rs("remark8") = (rem8.Text)
       rs("year6") = (co3.Text)
       rs("elp2") = (elp2.Text)
       rs("no2") = (no2.Text)
       rs("final9") = (fin9.Text)
       rs("final10") = (fin10.Text)
       rs("final11") = (fin11.Text)
       rs("final12") = (fin12.Text)
       rs("final13") = (fin13.Text)
       rs("final14") = (fin14.Text)
       rs("final15") = (fin15.Text)
       rs("re-xam9") = (re9.Text)
       rs("re-xam10") = (re10.Text)
       rs("re-xam11") = (re11.Text)
       rs("re-xam12") = (re12.Text)
       rs("re-xam13") = (re13.Text)
       rs("re-xam14") = (re14.Text)
       rs("re-xam15") = (re15.Text)
       rs("unit9") = (unit9.Text)
       rs("unit10") = (unit10.Text)
       rs("unit11") = (unit11.Text)
       rs("unit12") = (unit12.Text)
       rs("unit13") = (unit13.Text)
       rs("unit14") = (unit14.Text)
       rs("unit15") = (unit15.Text)
       rs("remark9") = (rem9.Text)
       rs("remark10") = (rem10.Text)
       rs("remark11") = (rem11.Text)
       rs("remark12") = (rem12.Text)
       rs("remark13") = (rem13.Text)
       rs("remark14") = (rem14.Text)
       rs("remark15") = (rem15.Text)
       rs("year7") = (summer1.co5.Text)
       rs("elp3") = (summer1.elp3.Text)
       rs("no3") = (summer1.no3.Text)
       rs("final16") = (summer1.fin16.Text)
       rs("final17") = (summer1.fin17.Text)
       rs("final18") = (summer1.fin18.Text)
       rs("final19") = (summer1.fin19.Text)
       rs("final20") = (summer1.fin20.Text)
       rs("re-xam16") = (summer1.re16.Text)
        rs("re-xam17") = (summer1.re17.Text)
        rs("re-xam18") = (summer1.re18.Text)
        rs("re-xam19") = (summer1.re19.Text)
        rs("re-xam20") = (summer1.re20.Text)
        rs("unit16") = (summer1.unit16.Text)
       rs("unit17") = (summer1.unit17.Text)
       rs("unit18") = (summer1.unit18.Text)
       rs("unit19") = (summer1.unit19.Text)
       rs("unit20") = (summer1.unit20.Text)
       rs("remark16") = (summer1.rem16.Text)
       rs("remark17") = (summer1.rem17.Text)
       rs("remark18") = (summer1.rem18.Text)
       rs("remark19") = (summer1.rem19.Text)
       rs("remark20") = (summer1.rem20.Text)
       
       rs.Update
       End If
       rs.Close: Set rs = Nothing
       con.Close: Set con = Nothing

any help will be appreciated

[si_the_geek]

In the table design, are any of the fields set to be a Primary Key?

If not you should set one (presumably on Num), and that is likely to get rid of the error (as well as give other benefits).


In terms of your code, you should remove the first rs.Update , because you haven't made any changes by then (.Update writes the changes you have made so far to the database). You do not need to tell the recordset to go in to editing mode, it will do that automatically as soon as you change any field values.

You should also change If rs.RecordCount <> 0 Then to If Not (rs.BOF AND rs.EOF) Then, because it is faster and more reliable.

There is no need to have brackets like this: rs("ncee") = (ncee.Text)

And finally, rather than use "magic numbers" like ,3,3 , you should use the enums provided (such as adOpenForwardOnly), as they make the code easier to read and maintain - and only require a tiny bit more typing (as you get the drop-down lists when you type the commas).

[Condomx]

so what code should be written then.?

[si_the_geek]

Erm... that is what most of my post explains (eg: "remove the first rs.Update").


If you mean the Primary Key, you don't use code - you do it within the database itself.

[Condomx]

must i set a primary key in the ms access database or not?,nahh didnt work,I remove the first rs.update and i change the If rs.RecordCount <> 0 Then to If Not (rs.BOF AND rs.EOF) Then but it always say "Query is too complex"..will thanks for your help anyway,

[si_the_geek]

must i set a primary key in the ms access database or not?

You definitely should, and not just for this table, but for every table in every database - because it is what allows the database system (and thus your code) to uniquely identify a record.

Having a PK reduces the amount of errors and bugs your program will get, as well as making the data safer (as you don't accidentally edit more records than you intended).


In addition to that (and all of the other things I mentioned), you should remove the line rs("num") = (num.Caption) , because you know (due to your Where clause) that it already has that value.

[Condomx]

can you write the code here please?

[Nightwalker83]

can you write the code here please?

Here is a VB 6.0 example from MS Access help:

' BeginPrimaryKeyVB
Sub Main()
    On Error GoTo PrimaryKeyXError

    Dim catNorthwind As New ADOX.Catalog
    Dim tblNew As New ADOX.Table
    Dim idxNew As New ADOX.Index
    Dim idxLoop As New ADOX.Index
    Dim colLoop As New ADOX.Column
    
    ' Connect the catalog
    catNorthwind.ActiveConnection = "Provider='Microsoft.Jet.OLEDB.4.0';" & _
        "Data Source='c:\Program Files\" & _
        "Microsoft Office\Office\Samples\Northwind.mdb';"
    
    ' Name new table
    tblNew.Name = "NewTable"
    
    ' Append a numeric and a text field to new table.
    tblNew.Columns.Append "NumField", adInteger, 20
    tblNew.Columns.Append "TextField", adVarWChar, 20
    
    ' Append new Primary Key index on NumField column
    ' to new table
    idxNew.Name = "NumIndex"
    idxNew.Columns.Append "NumField"
    idxNew.PrimaryKey = True
    idxNew.Unique = True
    tblNew.Indexes.Append idxNew
    
    ' Append an index on Textfield to new table.
    ' Note the different technique: Specifying index and
    ' column name as parameters of the Append method
    tblNew.Indexes.Append "TextIndex", "TextField"
    
    ' Append the new table
    catNorthwind.Tables.Append tblNew
    
    With tblNew
        Debug.Print tblNew.Indexes.Count & " Indexes in " & _
            tblNew.Name & " Table"

        ' Enumerate Indexes collection.
        For Each idxLoop In .Indexes
            With idxLoop
                Debug.Print "Index " & .Name
                Debug.Print "   Primary key = " & .PrimaryKey
                Debug.Print "   Unique = " & .Unique

                ' Enumerate Columns collection of each Index
                ' object.
                Debug.Print "    Columns"
                For Each colLoop In .Columns
                    Debug.Print "       " & colLoop.Name
                Next colLoop
            End With
        Next idxLoop

    End With

    ' Delete new table as this is a demonstration.
    catNorthwind.Tables.Delete tblNew.Name
    
    'Clean up
    Set catNorthwind.ActiveConnection = Nothing
    Set catNorthwind = Nothing
    Set tblNew = Nothing
    Set idxNew = Nothing
    Set idxLoop = Nothing
    Set colLoop = Nothing
    Exit Sub
    
PrimaryKeyXError:
    
    Set catNorthwind = Nothing
    Set tblNew = Nothing
    Set idxNew = Nothing
    Set idxLoop = Nothing
    Set colLoop = Nothing
    
    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
' EndPrimaryKeyVB

Not sure if that would help or not.

[Condomx]

i see nice...but i have no idea on how to merge that code to my code problem to be able to work..amm what i mean the code that i posted above!! i was pleasing to rewrite it to someone if he/she know's something idea on how to solve the "query is complex"..but thank you so much anyway..hope to get more appropriate answer soon..^_*

[si_the_geek]

The only things wrong with your code are what I explained in post #2, and you can fix all of that yourself. None of the mistakes you had should cause the error, but might cause other issues.

I recommend you show us what your code looks like after making those changes - because you might have missed something (or I might have!).



What Nightwalker83 posted is not meant to be part of your program, it is to set the Primary key of the table (which you can do manually instead, and only needs to be done once).



Apart from those issues, the only cause I can think of is your table design... you have lots of repeated fields (such as remark1 to remark20, final1 to final20, etc), which isn't the best idea - it makes many things more complex, including reading the data, searches, etc.

I suspect that most of those fields will usually be empty, and that you have just added 'too many' (you hope) so that you can store that many items if needed.

You should remove those repeated fields, and create new tables for them. For example, remark1 to remark20 should be removed and a new table (perhaps "remarks") created for them, which contains a field called remark (equivalent of remark1 to remark20) and a field which enables a link back to the table permanent (perhaps num, assuming it is unique within permanent).

You would then store multiple rows (if needed) in the remarks table for each row in the permanent table. It will be slightly more awkward to write the program (as you would need to use joins or multiple queries), but it will remove several bigger issues.

[Condomx]

ah ok thank you but can you explain a little about [rather than use "magic numbers" like ,3,3 , you should use the enums provided (such as adOpenForwardOnly), as they make the code easier to read and maintain]? please...

[si_the_geek]

If you don't know what Enums are, see the FAQ article What is an Enum, and how can I use it?. An example of Enums you probably use already is with MsgBox, which has vbYesNo etc. Many other things use them too, including ADO (such as with .Open).

In your code you have this:

 rs.Open "select * from permanent where num='" & num.Caption & "'", con, 3,3

If you look at the FAQ article What do the parameters of the recordset.Open method mean? you see that the parameters are meant to be readable things like adOpenForwardOnly etc, so your code might be the equivalent of this:

 rs.Open "select * from permanent where num='" & num.Caption & "'", con, adOpenForwardOnly, adLockOptimistic

..I say "might be", because like many people I honestly have no idea which of the possible values each of the 3s relates to - which means that we can't tell if you are creating problems for yourself by using the wrong values.


By using the Enums you can see at a glance what the parameters mean - rather than having to remember, or look them up.

The time to type the extra characters is ignorable, because when you type the commas (or type ad and then press Ctrl-Space) you get a list of the Enum, so you can just select one with the mouse or the Enter key.

[Condomx]

please see my file ...it just a test only...but when i fixed that problem i will apply it to my original project....because i cant fixed and i cant understand whats goind on it,it keeps saying query is complex in everytime i try'd to update a record...please here the link..and i promise theres no virus!!:



http://www.mediafire.com/file/zmmtygtyymo/test.rar

[si_the_geek]

Please don't upload your entire project and expect us to find the relevant code - you should just be showing us the relevant code, as you did at the start of this thread.


Based on the duplicate thread you posted yesterday, I assume you still have the extra rs.Update in there - which you should have fixed 2 weeks ago (I explained way back in post #2).


The only thing I have looked at from your attachment is the database, and it is safe to say that it is one of the worst database designs I have ever seen.

To start with you haven't bothered to think at all about the data types of any of the fields - you have just set them all to be Text(50). As you have some programming experience, that is very bad.

The other issue is that you have used just one table, when there should be several more - as I explained in post #10. If you need more help to do it, ask specific questions about it.


Even if you don't separate it out into multiple tables, you should remove all of the remark/final/... fields - and that (along with the blatant code error I have reminded you about) will almost certainly fix the error you are getting.

[Condomx]

ah ok i see...ahhah i didnt designed that program yet..