Saving records in a form using a recordset on a cmd button.

[JeffBarker]

Hi all - apologies if any of the following is unclear, I'm just starting out on my VBA career and my boss/tutor is away from the office today!!

I have created a form which is used to check transfer orders placed on another form elsewhere in the database - once the user has checked the order details they tick a yes/no box, and then depending on where the order is going a fax or email will be generated, selected from two seperate buttons on the form.

The email button has the following code behind it:

Private Sub cmdEmail_Order_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsdate As DAO.Recordset
Dim SQL As String

SQL = "SELECT TF.ID, Whole.[Transfer Order Information], Whole.[TFO Contact], TF.DateSent, TF.Company, TF.Ad1, TF.Ad2, TF.Ad3, TF.Town, TF.Pcode, TF.Telephone, TF.Contact, TF.Notes, TF.[4_Qty], Whole.[Wholesaler Promotion] " & _
"FROM tblWholesalers as Whole INNER JOIN tblTFOrders as TF ON Whole.ID = TF.WholesalerID " & _
"WHERE TF.LTChecked=-1 AND Whole.[TFO Method]='EMAIL' AND TF.DateSent is null"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
Set rsdate = db.OpenRecordset("tblTFOrders", dbOpenDynaset)

If Not rs.EOF And Not rs.BOF Then
rs.MoveFirst

Do Until rs.EOF
SendEmail [Transfer Order Information], -True, "Customer Transfer Order", "To: " & rs![TFO Contact] & vbCrLf & _
"Email: " & rs![Transfer Order Information] & vbCrLf & _
"Date: " & rs!DateSent & vbCrLf & vbCrLf & _
"Site Details:" & vbCrLf & vbCrLf & _
rs!Company & vbCrLf & _
rs!Ad1 & vbCrLf & _
rs!Ad2 & vbCrLf & _
rs!Ad3 & vbCrLf & _
rs!Town & vbCrLf & _
rs!Pcode & vbCrLf & vbCrLf & _
"Telephone: " & rs!Telephone & vbCrLf & _
"Contact: " & rs!Contact & vbCrLf & vbCrLf & _
"Notes: " & rs!Notes & vbCrLf & vbCrLf & _
"Order Details:" & vbCrLf & vbCrLf & _
"Product: Maggi Instant Gravy" & vbCrLf & _
"Pack Size: 1.74kg Tub" & vbCrLf & _
"Quantity: " & rs![4_Qty] & vbCrLf & _
"Promotion: " & rs![Wholesaler Promotion] & vbCrLf & vbCrLf & _
"If for any reason this order cannot be placed on the system within 1 working day of " & rs!DateSent & " please call **** and ask for Mr Smith"
rsdate.FindFirst "ID =" & rs!ID
rsdate.Edit
rsdate!DateSent = Date
rsdate.Update

rs.MoveNext
Loop

Else

MsgBox "No emails to export"

End If
End Sub

Currently, once the check box has been ticked I have to browse to the next record and back again before the code recognizes that the order is there, and sometimes I even have to close the form completely and go back in in order for the code to work.

Would anyone be able to help or have any suggestions on how to get this working so that the order record is saved or gets updated before pressing the Email button, please?

Again, apologies if any of this is unclear...I'm still learning, and my trusty VBA for Dummies book isn't particularly helpful in this instance!

Many Thanks,

Jeff.

 

[JamesMcS]

Hi! I would imagine you'd have requery the form, using

me.requery

Mmmm... maggi gravy....

 

[JeffBarker]

Hi James,

I'll give that a go...thanks.

How'd you get the Maggi Gravy connection?!

 

[JamesMcS]

One of my friends at uni used to use the Maggi savoury sauce in everything, he converted me....

 

[JeffBarker]

Ha, ha - yeah, you can't beat it!!

 

[JamesMcS]

Might treat myself to a bottle at Christmas! Haven't used it in donkey's..... let me know if requery doesn't work buddy

 

[jzwp22]

Is the form on which the button is located bound to the orders table? If so, the requery that JamesMcS mentioned should work. However, I would think that since the command will impact multiple records, the button should not be on a bound form, but rather on an unbound form. Then after the code executes, open the bound form filtered to just show the records that were updated. Also, it is usually good practice to close the recordsets and set the objects to nothing at the end of the procedure.

rs.close
SET rs=nothing

 

[JeffBarker]

Is the form on which the button is located bound to the orders table? If so, the requery that JamesMcS mentioned should work. However, I would think that since the command will impact multiple records, the button should not be on a bound form, but rather on an unbound form. Then after the code executes, open the bound form filtered to just show the records that were updated. Also, it is usually good practice to close the recordsets and set the objects to nothing at the end of the procedure.

rs.close
SET rs=nothing

Hi there - thanks for replying.

The form is bound to a query which pulls in data from two tables, one with order and customer information and one with wholesaler information on it (ie where the order is going).

The way I need the form work is to be able to view the orders that need double-checking before the tick box is checked to say that they're okay to send. You then press the relevant button (Fax or Email) and the database generates an output, depending on which media the wholesaler has chosen to receive.

How would you forsee this working on an unbound form?

 

[JeffBarker]

Might treat myself to a bottle at Christmas! Haven't used it in donkey's..... let me know if requery doesn't work buddy

Hi James,

Unfortunately that didn't work!! I put it at the end of the procedure (along with rs.close and SET res = nothing), right before the closing End Sub.

 

[jzwp22]

Usually when I deal with an update to multiple records, I use an unbound form with perhaps a list box from which the user selects the various records to update. If I need to do something specific to a record, then I would have the code execute while the form shows that record. In your case, why not have the code execute when the user checks the tick box that says that the email/fax for the order is okay to send (after update event of the tick box). With that approach you can pull the info from the form directly for the current record, and do the update to the date sent without opening two recordsets (you would just use the form's recordset)

 

[JeffBarker]

Usually when I deal with an update to multiple records, I use an unbound form with perhaps a list box from which the user selects the various records to update. If I need to do something specific to a record, then I would have the code execute while the form shows that record. In your case, why not have the code execute when the user checks the tick box that says that the email/fax for the order is okay to send (after update event of the tick box). With that approach you can pull the info from the form directly for the current record, and do the update to the date sent without opening two recordsets (you would just use the form's recordset)

Apologies...I'm a bit confused here...would it just be a case of copying the above code into the AfterUpdate event of the tick box??

If so, how would that work when the user is pressing the Fax or Email buttons? The overall idea for the DB is so a user can go through orders one by one, ticking each one as okay to send as they do so, and then generating a bunch of Faxes or Emails at the end of a batch.

 

[jzwp22]

You would not need the fax/e-mail buttons at all since you said the following which implies that you have the wholesaler's choice stored somewhere:

..on which media the wholesaler has chosen to receive

The code you currently have is for updating multiple records since you are looping through a recordset, whereas if you put code in the after update event of the tick box that says that the record is OK to send, you will only be working with the currently displayed record which requires somewhat simpler code.

Also, since the code would executer for each record, you keep with your DB premise:

The overall idea for the DB is so a user can go through orders one by one

 

[JeffBarker]

You would not need the fax/e-mail buttons at all since you said the following which implies that you have the wholesaler's choice stored somewhere:



The code you currently have is for updating multiple records since you are looping through a recordset, whereas if you put code in the after update event of the tick box that says that the record is OK to send, you will only be working with the currently displayed record which requires somewhat simpler code.

Also, since the code would executer for each record, you keep with your DB premise:

Yes. The query that runs this form pulls data from the Wholesaler table, which has a field that denotes which type of media (Fax/Email) the company wishes to receive.

So instead of generating a number of Faxes or Emails at the end of the order checking process, the preselected media would appear on a case-by-case basis, meaning it could be sent or printed by the user before they moved on to checking the next order and so on?

How would I go about cutting down the code I have to accommodate this simpler process?

Thanks again for all your assistance...it's greatly appreciated.

 

[jzwp22]

To start, you would not need the code for either recordset. I would make sure that all of the info you need for either the fax or e-mail is brought in via the query to which the form is bound and have corresponding controls on the form (if you don't want your users to see this info, just change the visible property of the control to No).

You would have a simple IF..THEN.. ELSE...END IF, something like the following I just changed the rs! to me. which would reference corresponding controls on your form

[COLOR="Red"]IF me.control="EMAIL" THEN[/COLOR]

SendEmail [Transfer Order Information], -True, "Customer Transfer Order", "To: " & me.[TFO Contact] & vbCrLf & _
"Email: " & me.[Transfer Order Information] & vbCrLf & _
"Date: " & me.DateSent & vbCrLf & vbCrLf & _
"Site Details:" & vbCrLf & vbCrLf & _
me.Company & vbCrLf & _
me.Ad1 & vbCrLf & _
me.Ad2 & vbCrLf & _
me.Ad3 & vbCrLf & _
me.Town & vbCrLf & _
me.Pcode & vbCrLf & vbCrLf & _
"Telephone: " & me.Telephone & vbCrLf & _
"Contact: " & me.Contact & vbCrLf & vbCrLf & _
"Notes: " & me.Notes & vbCrLf & vbCrLf & _
"Order Details:" & vbCrLf & vbCrLf & _
"Product: Maggi Instant Gravy" & vbCrLf & _
"Pack Size: 1.74kg Tub" & vbCrLf & _
"Quantity: " & me.[4_Qty] & vbCrLf & _
"Promotion: " & me.[Wholesaler Promotion] & vbCrLf & vbCrLf & _
"If for any reason this order cannot be placed on the system within 1 working day of " & Date() & " please call **** and ask for Mr Smith" 

'populate the rsdate control on the form with the current date
me.rsdate = Date()

[COLOR="red"][COLOR="Red"]ELSE[/COLOR][/COLOR]

code to execute the fax option goes here
'populate the rsdate control on the form with the current date
me.rsdate = Date

[COLOR="Red"]END IF[/COLOR]

 

[JeffBarker]

To start, you would not need the code for either recordset. I would make sure that all of the info you need for either the fax or e-mail is brought in via the query to which the form is bound and have corresponding controls on the form (if you don't want your users to see this info, just change the visible property of the control to No).

You would have a simple IF..THEN.. ELSE...END IF, something like the following I just changed the rs! to me. which would reference corresponding controls on your form

[COLOR=red]IF me.control="EMAIL" THEN[/COLOR]
 
SendEmail [Transfer Order Information], -True, "Customer Transfer Order", "To: " & me.[TFO Contact] & vbCrLf & _
"Email: " & me.[Transfer Order Information] & vbCrLf & _
"Date: " & me.DateSent & vbCrLf & vbCrLf & _
"Site Details:" & vbCrLf & vbCrLf & _
me.Company & vbCrLf & _
me.Ad1 & vbCrLf & _
me.Ad2 & vbCrLf & _
me.Ad3 & vbCrLf & _
me.Town & vbCrLf & _
me.Pcode & vbCrLf & vbCrLf & _
"Telephone: " & me.Telephone & vbCrLf & _
"Contact: " & me.Contact & vbCrLf & vbCrLf & _
"Notes: " & me.Notes & vbCrLf & vbCrLf & _
"Order Details:" & vbCrLf & vbCrLf & _
"Product: Maggi Instant Gravy" & vbCrLf & _
"Pack Size: 1.74kg Tub" & vbCrLf & _
"Quantity: " & me.[4_Qty] & vbCrLf & _
"Promotion: " & me.[Wholesaler Promotion] & vbCrLf & vbCrLf & _
"If for any reason this order cannot be placed on the system within 1 working day of " & Date() & " please call **** and ask for Mr Smith" 
 
'populate the rsdate control on the form with the current date
me.rsdate = Date()
 
[COLOR=red][COLOR=red]ELSE[/COLOR][/COLOR]
 
code to execute the fax option goes here
'populate the rsdate control on the form with the current date
me.rsdate = Date
 
[COLOR=red]END IF[/COLOR]

That's super helpful - thank you so much! I will crack on with this right away...I'll reply back here to let you know either way.

Thanks again!

 

[jzwp22]

You're welcome.

 

[JeffBarker]

Good morning.

I've added your code in and it does work, apart from a couple of very minor things which I'm hoping you may be able to advise on please??

 

[jzwp22]

I didn't know if it would as I had it; I thought you might need to tweak it a bit more, but I'm glad it worked for you. What additional help did you need?

 

[JeffBarker]

I didn't know if it would as I had it; I thought you might need to tweak it a bit more, but I'm glad it worked for you. What additional help did you need?

Hello - thanks again!

The email/fax appears when you tick the check box, but also when you un-tick it as well, and for some reason the email/fax appears for the first record only...so even if you're four or five records down the line, when you tick the box a fax/email for the first record you did will appear, instead of one for whichever record you're currently on.

 

[jzwp22]

Oops, my error... You will have to embed the code I provided earlier within another IF..THEN. If the check box is checked, its value is -1, so we need to make sure it is checked prior to executing the code. If it is unchecked the value =0 and you don't want to do anything else I assume, so we do not need the ELSE portion of the clause.

If me.checkboxname=-1 Then

code provided earlier


End if

...so even if you're four or five records down the line, when you tick the box a fax/email for the first record you did will appear, instead of one for whichever record you're currently on

What style of form are you using (datasheet view, form view, continuous etc.)?