By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,772 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Ticking several boxes in a sub form

100+
P: 116
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.

Expand|Select|Wrap|Line Numbers
  1. If Me.Close.Value = True Then
  2. Me.frm_sub_deliverynote!Close.Value = True
  3. End if
  4.  
When I do this it only ticks the record at the top of the list and not the rest?

Thanks

Jacc14
Oct 8 '08 #1
Share this Question
Share on Google+
14 Replies


DonRayner
Expert 100+
P: 489
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
Oct 8 '08 #2

100+
P: 116
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.

Expand|Select|Wrap|Line Numbers
  1.          .Fields("Delnumber") = frm_sub_deliverynote.Form.orderno & " / " & frm_sub_deliverynote.Form.Delsplit
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Quicktickdel_Click()
  2.  
  3. Dim db As DAO.Database
  4. Dim rs As DAO.Recordset
  5. Set db = CurrentDb
  6. Set rs = Me.frm_sub_deliverynote.Form.RecordsetClone
  7.  
  8. With rs
  9.         .MoveFirst
  10.  
  11.       Do Until .EOF = True
  12.         If Me.Quicktickdel = True Then
  13.           .Edit
  14.           .Fields("printdel") = True
  15.  
  16.           .Fields("Delnumber") = frm_sub_deliverynote.Form.orderno & " / " & frm_sub_deliverynote.Form.Delsplit
  17.  
  18.           .Update
  19.           .MoveNext
  20.  
  21.  
  22.  
  23.           ElseIf Me.Quicktickdel = False Then
  24.           .Edit
  25.           .Fields("printdel") = False
  26.           .Fields("Delnumber") = ""
  27.           .Update
  28.           .MoveNext
  29.  
  30.  
  31.  
  32.         Else
  33.           .MoveNext
  34.         End If
  35.       Loop
  36.  
  37.  
  38. End With
  39.  
  40. rs.close
  41. Set rs = Nothing
  42. Set db = Nothing
  43. Me.Requery
  44.  
  45.  

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

Expand|Select|Wrap|Line Numbers
  1. 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));"
  2.  
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
Oct 9 '08 #3

DonRayner
Expert 100+
P: 489
You are trying to add the " to your string so you need to enclose them with single quotes IE :
Expand|Select|Wrap|Line Numbers
  1. '"' / '"'
  2. instead of
  3. " / "
Oct 9 '08 #4

100+
P: 116
Hi there.
Well with a bit of tweeking the code I got it to work.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "UPDATE Duplicate_despatches " & _
  2.              "INNER JOIN tbl_confirmation " & _
  3.              "ON Duplicate_despatches.orderno = tbl_confirmation.orderno " & _
  4.              "SET Duplicate_despatches.delnumber = frm_sub_deliverynote.Form.orderno " & _
  5.              "& ' / '  & frm_sub_deliverynote.Form.Delsplit " & _
  6.              "WHERE (((duplicate_despatches.printdel)= true " & _
  7.              "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
Oct 10 '08 #5

NeoPa
Expert Mod 15k+
P: 31,709
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.
Oct 10 '08 #6

NeoPa
Expert Mod 15k+
P: 31,709
Hi there.
Well with a bit of tweeking the code I got it to work.

Expand|Select|Wrap|Line Numbers
  1. 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]));"
  2.  
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.
Oct 10 '08 #7

100+
P: 116
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
Oct 10 '08 #8

NeoPa
Expert Mod 15k+
P: 31,709
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.
Oct 10 '08 #9

NeoPa
Expert Mod 15k+
P: 31,709
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.
Oct 10 '08 #10

100+
P: 116
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.
Oct 11 '08 #11

DonRayner
Expert 100+
P: 489
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.
Oct 11 '08 #12

NeoPa
Expert Mod 15k+
P: 31,709
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?
Oct 11 '08 #13

100+
P: 116
Thats done the trick. Thanks for the guidance as going live today.

Best regards
Christine.
Oct 13 '08 #14

NeoPa
Expert Mod 15k+
P: 31,709
Very pleased it's working for you now :)

Let us know if you have any further problems.
Oct 13 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.