Ticking several boxes in a sub form

[jacc14]

Hi there.

I am sure there is an easy solution to this and not sure why mine doesnt work?

In the main form I have a "closed"tick box. In the sub form I also have several related records each of which have a "closed" tick box.

What i want to do is tick the "closed" box in the main form and it automatically tick the "closed" boxes in the sub form.

I tried this.

If Me.Close.Value = True Then
Me.frm_sub_deliverynote!Close.Value = True
End if
 

When I do this it only ticks the record at the top of the list and not the rest?

Thanks

Jacc14

[DonRayner]

It's not working because even though you can see all of the records in the subform only one of them is the current record. You will have to step through the records in the subform using the RecordsetClone Property and update them one at a time

Don

[jacc14]

Hi there.

Thanks for putting me in the right direction. I can see now why it wasnt working. I have now successfully put in the following code and the tick is applied to all

However I am also wanting to apply a delivery note number by using the order number and adding a "/" and urn number that belongs to each record.

         .Fields("Delnumber") = frm_sub_deliverynote.Form.orderno & " / " & frm_sub_deliverynote.Form.Delsplit
 

I thought this would be simple and work as before but it just applies the current records order number / urn to all records even though its looping through.

THIS IS HOW I DID IT

Private Sub Quicktickdel_Click()
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = Me.frm_sub_deliverynote.Form.RecordsetClone
 
With rs
        .MoveFirst
 
      Do Until .EOF = True
        If Me.Quicktickdel = True Then
          .Edit
          .Fields("printdel") = True
 
          .Fields("Delnumber") = frm_sub_deliverynote.Form.orderno & " / " & frm_sub_deliverynote.Form.Delsplit
 
          .Update
          .MoveNext
 
 
 
          ElseIf Me.Quicktickdel = False Then
          .Edit
          .Fields("printdel") = False
          .Fields("Delnumber") = ""
          .Update
          .MoveNext
 
 
 
        Else
          .MoveNext
        End If
      Loop
 
 
End With
 
rs.close
Set rs = Nothing
Set db = Nothing
Me.Requery
 
 

As this didnt work I removed the line and ran an sql query as below.

DoCmd.RunSQL "UPDATE Duplicate_despatches INNER JOIN tbl_confirmation ON Duplicate_despatches.orderno = tbl_confirmation.orderno SET Duplicate_despatches.delnumber = frm_sub_deliverynote.Form.orderno & " / " & frm_sub_deliverynote.Form.Delsplit WHERE (((duplicate_despatches.printdel)= true));"
 

But I get a type mismatch. It works if just putting in order number but not when adding the "/". Incidently it is a text field.

Hope this all makes sense and I wonder if you could send me in the right direction please

Jacc14

[DonRayner]

You are trying to add the " to your string so you need to enclose them with single quotes IE :

'"' / '"'
instead of
" / "

[jacc14]

Hi there.
Well with a bit of tweeking the code I got it to work.

DoCmd.RunSQL "UPDATE Duplicate_despatches " & _
             "INNER JOIN tbl_confirmation " & _
             "ON Duplicate_despatches.orderno = tbl_confirmation.orderno " & _
             "SET Duplicate_despatches.delnumber = frm_sub_deliverynote.Form.orderno " & _
             "& ' / '  & frm_sub_deliverynote.Form.Delsplit " & _
             "WHERE (((duplicate_despatches.printdel)= true " & _
             "and Duplicate_despatches.orderno = [forms]![mainform_search]![orderno]));"

However it is putting the same number down for every record. eg just picking up the first number in the list. Is running the sql statement therefore the wrong way to go? The order number is correct as thats the same anyway but the delsplit should change for each record.

Thanks

Jacc14

[NeoPa]

As this didnt work I removed the line and ran an sql query as below.

Your references to frm_sub_deliverynote.Form.orderno and frm_sub_deliverynote.Form.Delsplit are not valid from within SQL. The SQL engine is not a part of your form, nor does it understand VBA references.

As an external reference it needs to be explicit.

[NeoPa]

Hi there.
Well with a bit of tweeking the code I got it to work.

DoCmd.RunSQL "UPDATE Duplicate_despatches INNER JOIN tbl_confirmation ON Duplicate_despatches.orderno = tbl_confirmation.orderno SET Duplicate_despatches.delnumber = frm_sub_deliverynote.Form.orderno & ' / '  & frm_sub_deliverynote.Form.Delsplit WHERE (((duplicate_despatches.printdel)= true and Duplicate_despatches.orderno = [forms]![mainform_search]![orderno]));"
 

However it is putting the same number down for every record. eg just picking up the first number in the list. Is running the sql statement therefore the wrong way to go? The order number is correct as thats the same anyway but the delsplit should change for each record.

Thanks

Jacc14

Frankly, displaying your code in a single line of 366 characters is not a very friendly way to ask for help. You've been here a while now and I expect you to phrase your questions in a way that doesn't require me to take it and view it in a separate application so I can tell what you're talking about.

[jacc14]

Dear NeoPa

I am once again gutted that i managed to offend. Since my last telling off about how i present things i have been really careful to always read the guidelines, always thank the person after, make sure its clear and can be understood. If you look at my history I am sure you will find an improvement.

I have hardly used sql and dont really know how to put on to lots of lines. If it fails then I wont know if its because of the code or the fact that i have incorrectly laid it out. I havent had any training in any of the work I do, I just rely on books and the forum to gain my knowledge.

I guess I should abandon my sql theory and stick to what i know. The recordset idea worked for the ticks but not for the numbering. If you or anyone else is willing to help me then I would be extremely grateful.

Best regards
Christine

[NeoPa]

Christine,

Maybe I'm just too prickly - and more so when under pressure.

If you say you're trying - I can live with that. It's always hard to know when someone is simply being careless, or they are just playing in an area where they are simply unfamiliar. I really have nothing against failing to do things properly. It's only when people don't bother to try that I get rattled. I have to accept that you don't fall under the latter category.

I will reformat it for you, and look at what may be the problem when I've done that.

[NeoPa]

Right, firstly I think you need to replace any relative references to items on your form with specific ones. This is the point I was trying to express in post #6. You have to remember that, while the code in the module of your form can refer to items on the form as Me.blahblahblah (blahblahblah references in VBA without the Me. part have the Me. part assumed), any SQL that you run is done from a place where it is not in the form (even when called from VBA within the form), so relative references like that are invalid. In your SQL (see the new line #7) you refer to [forms]![mainform_search]![orderno] which is perfectly valid. frm_sub_deliverynote.Form.orderno, however (lines #4 & #5), is not. That cannot be interpreted by the SQL engine.

Moving on from that, I didn't give a more definitive answer before (when you were still considering the VBA approach), simply because I didn't have a clear enough understanding of the question. There were too many items that formed parts of the puzzle, but which weren't explained. It gets quite hard to understand what a question is about when you don't understand what all the building blocks are. Often, we get enough to recognise what it's probably about and we may make lucky guesses (I know I often do), but it works better when we really understand the question.

I know this sounds like another moan, but I suspect that your original approach was probably one that could work with a bit of tweaking. I just don't understand the problem well enough to guide at this point. Sorry.

Alternatively, we could continue with the SQL approach, but only if you publish what the items are you refer to in the SQL. Also, as your understanding of the SQL may be flawed, it would help to understand what is a reference on the form to a single record, and what is supposed to refer to each record that is updated. Again, normally I can reverse engineer SQL code, but I have to have some basic understanding of what's what to do that.

[jacc14]

Thank you for you reply , dont feel so beaten upon now ;). I must assure you that I am really trying and with the pressures of work and tight deadlines sometimes have to go different routes which are out of my area hence going the sql route in the hopes for a quick fix

I have decided to abandon the sql route and try to get my original code working. I have inserted on #16 the code which i thought would work but it doesnt. I am going to search the forum to see if anyone has solved this.

Many Thanks
Christine.

[DonRayner]

On line 16 of your origional code you are refering back to the form. Again, since only one record will be the current one, that is the one where you are getting the data from.

You need to pull the data from the record that you are currently sitting at in the recordsetclone not the origional recordset of the form.

[NeoPa]

I suspected that may be the case, but was a little lost trying to interpret exactly what was being said. That also has the benefit of being in VBA so the form references (Me.BlahBlahBlah) should work fine too.

That sounds like a perfect answer Don :)

Does that make sense to you Christine?

[jacc14]

Thats done the trick. Thanks for the guidance as going live today.

Best regards
Christine.

[NeoPa]

Very pleased it's working for you now :)

Let us know if you have any further problems.