 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / February 2006Delete records with only Name and Date filled in
Hello - I have a form that has Name, Date and various other fields in it. The Name is automatically filled in from the user's logon name and the date is autofilled too. Problem: The user enters data, then hits a button taking them to a new blank record (which isn't really blank because it has the user name and date in it). When they exit, a record is left in the table because of the autofills. Is there a way I can delete the record on exit? Rick B had suggested that I use the before update event or before insert event, which sounded obvious to me, but when I went back to fix this, I realized the date and name really have to be visible when each record starts. Any help will be greatly appreciated!  SignatureSandy Just build a select query and pull all records where the name and date are not null, but the others are. Run it to make sure it pulls what you expect. Then change it to a delete query and run it. SignatureRick B > Hello - > [quoted text clipped - 15 lines] > > Any help will be greatly appreciated! Thanks, Rick. I'll give it a try! SignatureSandy > Just build a select query and pull all records where the name and date are > not null, but the others are. Run it to make sure it pulls what you expect. [quoted text clipped - 20 lines] > > > > Any help will be greatly appreciated! Can you use the default value property of the relevant controls to do this show the values? It appears that you are calculating the values and assigning them to the controls. > Hello - > [quoted text clipped - 15 lines] > > Any help will be greatly appreciated! Hi John - Thanks for your response. I tried both the default value in the table and then figured the best thing would probably be the default value of the controls themselves. Either way, when a new record is produced, if there's a default value, it puts it into the next record. SignatureSandy > Can you use the default value property of the relevant controls to do this > show the values? [quoted text clipped - 21 lines] > > > > Any help will be greatly appreciated! Default values display in the control when a new record is displayed, but are not assigned to the record field until the record is dirtied by some action or the record is saved. Do you have code that runs and assigns values to any fields? If you do, that will dirty the record and the default values will automatically be assigned to the relevant fields. > Hi John - > [quoted text clipped - 34 lines] >> > >> > Any help will be greatly appreciated! Hi John - Thanks for responding. I have the following in Form Load: DoCmd.ApplyFilter , "Agent='" & fOSUserName & _ "' AND CallDate=" & Date Also, there is a query attached to the form. The source for Agent is the Agent column in the query and the source for CallDate is the CallDate column in the query. I don't know how that would automatically create a new record, but if I switch from the form to my table, when I move to a new record for my form, a row is automatically put in the table with the Agent and CallDate. SignatureSandy > Default values display in the control when a new record is displayed, but > are not assigned to the record field until the record is dirtied by some [quoted text clipped - 40 lines] > >> > > >> > Any help will be greatly appreciated! Dates need to be delimited with # symbols, and need to be in mm/dd/yyyy format (Okay, the latter isn't strictly true: you can use any unambiguous format, such as yyyy-mm-dd or dd mmm yyyy. The point is, if your short date format is dd/mm/yyyy, it won't work for the first 12 days of each month) Try: DoCmd.ApplyFilter , "Agent='" & fOSUserName & _ "' AND CallDate=" & Format(Date, "\#mm\/dd\/yyyy\#") SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > Hi John - > [quoted text clipped - 66 lines] >> >> > >> >> > Any help will be greatly appreciated! Hi Doug - Thanks for your response. I haven't had any problem with my date formatting, although that's a good heads-up for the future. I have a default value of Now() for the textbox. My BIG problem is that everytime a new "blank" record is created, (acNew), automatically a row is created in the table and the Agent name and Date are stuck in. I don't know why this is happening. It gives me the really huge problem of creating blank records. Any thoughts on the above? SignatureSandy > Dates need to be delimited with # symbols, and need to be in mm/dd/yyyy > format (Okay, the latter isn't strictly true: you can use any unambiguous [quoted text clipped - 76 lines] > >> >> > > >> >> > Any help will be greatly appreciated! I haven't been following the thread, but you mention Rick B's suggestion to use the BeforeUpdate event or BeforeInsert event. Can you perhaps check whether any of the other fields have values in them, and cancel the event if they don't? SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi Doug - > [quoted text clipped - 89 lines] > > >> >> > > > >> >> > Any help will be greatly appreciated! Hi Douglas - It's too late to just cancel the event, because the record is already created. Rick B's delete suggestion works, but not only are my autonumbers messed up (I know that doesn't really matter, it just bugs me . . .), there's the problem of an unnecessary trip to the database. Any other thoughts? SignatureSandy > I haven't been following the thread, but you mention Rick B's suggestion to > use the BeforeUpdate event or BeforeInsert event. Can you perhaps check [quoted text clipped - 115 lines] > > > >> >> > > > > >> >> > Any help will be greatly appreciated! Do you have any code in the Current event of the form or any code in other form events that sets the values of any of the controls (or fields) on the form? I would expect the CallDate control to have a default value of Date() [or Now() if you wanted the date and time] and the Agent Control to have the default value set to fOSUser(). As I expect you know, if you set these as defaults, they will appear in the form on the new record, but will not be set as values in the record until something triggers the form to create a new record. > Hi Douglas - > [quoted text clipped - 146 lines] >> > > >> >> > >> > > >> >> > Any help will be greatly appreciated! Hi John - Thanks again for replying. I do have a Requery in the current event of the form. Would that do it? I need the requery because I have two comboboxes and one textbox; whatever the person picks in the first combo narrows the choices in the second combo and whatever the person chooses in the second combo determines the textbox. By the way, I've had a very difficult time doing the combo thing and I still can't get the records to display by navigating previous records. At any rate, is the requery the culprit? SignatureSandy > Do you have any code in the Current event of the form or any code in other > form events that sets the values of any of the controls (or fields) on the [quoted text clipped - 158 lines] > >> > > >> >> > > >> > > >> >> > Any help will be greatly appreciated! Unfortunately, there's no way to get around the Autonumber issue. Autonumbers are intended for one purpose: to provide a (practically guaranteed) unique value that can be used as a primary key. If the value of the number matters to you, you probably shouldn't be using an Autonumber. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi Douglas - > [quoted text clipped - 124 lines] > > > > >> >> > > > > > >> >> > Any help will be greatly appreciated! Hi Doug - It doesn't really matter to me, it just seems neater. SignatureSandy > Unfortunately, there's no way to get around the Autonumber issue. > Autonumbers are intended for one purpose: to provide a (practically [quoted text clipped - 155 lines] > > > > > >> >> > > > > > > >> >> > Any help will be greatly appreciated! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.