5/2/2024 8:32:12 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Basics: Saving edit box to SLXdb
Posted: 10 Apr 07 2:04 PM
|
This is an edited version of my previous message. I have created a form linked to contact mainview. This form has 3 edit boxes the pull first name, last name, and email from the SLX contact table. The purpose of this portion of the form is to verify contact data, if the fields need updated, they obviously need saved back to the saleslogix table. This is what I hae so far:
Dim rs
Dim cn
'Open the connection
Set cn = Application.GetNewConnection
'Create the Recordset object
Set rs = CreateObject("ADODB.Recordset")
'Set the cursor location (Client),
'Locktype and cursor type
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenStatic
'Clear the errors collection
cn.Errors.Clear
'Open the Recordset
rs.Open "SELECT FIRSTNAME, LASTNAME, EMAIL FROM CONTACT WHERE CONTACTID=Current ContactID"
'Put the recordset into Insert mode
rs.AddNew
'Set the field values
rs.Fields("FIRSTNAME") = "edFirstName.Text"
rs.Fields("LASTNAME") = "edLastName.Text"
rs.Fields("EMAIL") = "edEmail.Text"
'Call the update method
rs.UpdateBatch
'Check for errors
If cn.Errors.Count > 0 Then
MsgBox "Error: Please verify all fields"
End If
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
I am new to this, any help would be greatly appreciated. |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 11 Apr 07 9:56 AM
|
It seems there may be a number of ways to answer this message.
First, it sounds like you mentioned that the form is "linked" to the contact mainview - do you mean it is a data form tied to the contact table? If it is, then you won't need any code. You just need to bind the text fields to the data fields in the contact table, and then any changes made to the text fields will be saved to the table if the user clicks ok, and if the user clicks cancel, the changes won't be saved.
If it is a managed form, then you'll need all that code, but it looks like you'll need to make a lot of changes.
1) To open the recordset, you'll need to change your SQL statement to look like this: "SELECT FIRSTNAME, LASTNAME, EMAIL FROM CONTACT WHERE CONTACTID= '" & Application.BasicFunctions.CurrentContactID & "'"
2) It sounds like you are trying to update the information - in which case, don't put the recordset into insert mode - just take out the line that says "rs.AddNew"
3) When using text boxes, place quotations marks around the text box references - change "edFirstName.Text" to edFirstName.Text, and do the same for the other two. By placing quotation marks around the references, you are telling SalesLogix to place the word edFirstName.Text into the data field, removing the first name.
4) Instead of rs.UpdateBatch, just use rs.Update. This is more or less being picky, but you don't need rs.UpdateBatch because you aren't updating multiple records in the recordset.
Other than that, the script seems alright. I personally have never used the cn.Errors property, and I have never heard of it, but if that works, great!
Jeff |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 12 Apr 07 9:02 AM
|
| I appreciate your response Jeff, thanks. Your suggestions helped to get the script to run. However, this leads me to my next question for someone. I have 3 picklists on the same form. I chose picklists because of the ease to populate them with no coding involved. I believe I will have an issue later on because I need to export the selections made in the picklists. Would it make more sense if I used comboboxes instead so that the selections can be inserted into a SLX table that I have already created, or should I stay with the picklists? |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 12 Apr 07 9:38 AM
|
fiogf49gjkf0d You're reason for choosing the picklists is a good one.
What do you mean exactly by needing to export the picklists? Are they multi-select picklists, or single-select? If you are saving to the database, you can save to the database just as well with the picklist - you don't need comboboxes. |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 12 Apr 07 10:15 AM
|
fiogf49gjkf0d My intention with this form is to gather information to be saved in a SLX table that I created to later be exported to Excel along with the corresponding contact information. I changed the picklists to combo boxes prior to your reply, but either way I am at the same point. To save the selections to my new table, I would think I would go about the same process as I did above. Given the new table is empty, I am not all that certain the process. My thoughts are that I would have to check the table to see if items exists, if so, it would work like the edit boxes, but if not, I would have to insert a new record?
Obviously not correct, but this is how I started:
Dim rs
Dim cn
'Open the connection
Set cn = Application.GetNewConnection
'Create the Recordset object
Set rs = CreateObject("ADODB.Recordset")
'Set the cursor location (CLient),
'Locktype and cursor type
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenStatic
'Open the Recordset
rs.Open "SELECT Q10_CAMPAIGNID, CONTACTID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE, OUTSIDE_IMAGE, COPY_CONTENT, KEY_TAG_SELECTION FROM Q10_Campaign WHERE CONTACTID= '" & Application.BasicFunctions.CurrentContactID & "'", cn
'Checks to see if table is blank.
If rs.EOF Then
'Put the recordset into insert mode.
rs.AddNew
'Set the field values
Else
rs.Fields("Q10_CAMPAIGNID") = ""
rs.Fields("CONTACTID") = Application.BasicFunctions.CurrentContactID
rs.Fields("CREATEUSER") = Application.BasicFunctions.CurrentUserID
rs.Fields("CREATEDATE") = Now
rs.Fields("MODIFYUSER") = Application.BasicFunctions.CurrentUserID
rs.Fields("MODIFYDATE") = Now
rs.Fields("OUTSIDE_IMAGE") = cboCoverImage.Text
rs.Fields("COPY_CONTENT") = cboCopyContent.Text
rs.Fields("KEY_TAG_SELECTION") = cboKeytag.Text
'Call the update method
rs.Update
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End If
The else statement is shaky, but like I said, this is my start. |
|
|
| |
|
Re: Basics: Saving edit box to SLXdb
Posted: 16 Apr 07 1:48 PM
|
With this script, I now need to add some intelligence. I want a row to be saved in the table up to 10 times, 1 row for each keytag selection. I was able to get the script to write to the table, but could only make one selection per ContactID. Any thoughts?
Sub btnSaveClick(Sender)
Dim rs
Dim cn
'Open the connection
Set cn = Application.GetNewConnection
'Create the Recordset object
Set rs = CreateObject("ADODB.Recordset")
'Set the cursor location (CLient),
'Locktype and cursor type
rs.CursorLocation = adUseClient
rs.LockType = adLockBatchOptimistic
rs.CursorType = adOpenStatic
'Open the Recordset
rs.Open "SELECT CAMPAIGNID, CONTACTID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE, OUTSIDEIMAGE, COPYCONTENT, KEYTAG FROM Q10_CAMPAIGN WHERE CONTACTID= '" & Application.BasicFunctions.CurrentContactID & "'", cn
'Checks to see if table is blank
If rs.EOF Then
'Put the recordset into insert mode
rs.AddNew
'Checks for duplicate data entry
ElseIf rs.Fields("KEYTAG") = cboKeytag.Text Then
MsgBox "That keytag has already been selected, please choose another", 48, "Error"
Else
'Set the field values
rs.Fields("CAMPAIGNID") = "??"
rs.Fields("CONTACTID") = Application.BasicFunctions.CurrentContactID
rs.Fields("CREATEUSER") = Application.BasicFunctions.CurrentUserID
rs.Fields("CREATEDATE") = Now
rs.Fields("MODIFYUSER") = Application.BasicFunctions.CurrentUserID
rs.Fields("MODIFYDATE") = Now
rs.Fields("OUTSIDEIMAGE") = cboCoverImage.Text
rs.Fields("COPYCONTENT") = cboCopyContent.Text
rs.Fields("KEYTAG") = cboKeytag.Text
'Call the update method
rs.Update
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End If
End Sub |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 17 Apr 07 10:54 AM
|
To do something multiple times per anything, you should incorporate a loop - unless I am missing something here. Are you saying that the select statement you are running could have more than one record? If I am not missing something here, then I recommend the Do...Loop. It goes something like this
Else 'From your code
rs.MoveFirst
Do Until rs.EOF
'Set the field values
rs.MoveNext
Loop
'Call the update method
rs.UpdateBATCH 'you had this in your first post, but didn't need it then while updating only one record
And that's it for the loop. Search the internet with Google or whatever to lookup performing loops in VBScript - tons of articles out there. Another loop commonly used in SalesLogix is the For...Next loop, and there are several examples in the code. Again, forgive me if this isn't what you meant, and if you already know how to handle loops.
Also, it looks like (and I am guessing here) that the table you are selecting from is a 1:1 table off of Campaign, and it always applies to the current contact. This means that you can move the lines that start with rs.Fields("CAMPAIGNID") and rs.Fields("CONTACTID") to the space right under rs.AddNew, because you would only set these values if this was a new recordset. rs.Fields("CAMPAIGNID") should also be set to an existing campaign ID. Lastly, you don't need to set the create and modify fields - the provider will do this for you. |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 18 Apr 07 9:17 AM
|
| I understand Loop statements, but I do not know if they will do what I want here. For clarity's sake, if there are no rows in the table for the current contactid, then rs.AddNew. But after that if another selection is to be made, I want to maintain the previous selection and add an additional row for that same contactID. So I would think I would have to call rs.AddNew again. I believe I should use a loop to check for duplicates, i.e. For each if rs.Fields("KEYTAG") = cboKeytag.text Then MsgBox "Keytag has already been selected..." Next. My real issue is not being able to figure out how to save multiple rows, keytag selections, for 1 contactID. |
|
|
| |
|
Re: Basics: Saving edit box to SLXdb
Posted: 19 Apr 07 8:15 AM
|
| I appreciate the response Bob, but I have previously read through that article. Saving a record is not my issue, saving multiple records for one ContactID is. Thanks though. |
|
|
| |
|
Re: Basics: Saving edit box to SLXdb
Posted: 20 Apr 07 8:31 AM
|
Originally posted by Anthony Martie
With this script, I now need to add some intelligence. I want a row to be saved in the table up to 10 times, 1 row for each keytag selection. I was able to get the script to write to the table, but could only make one selection per ContactID. Any thoughts?
Sub btnSaveClick(Sender)
....
'Open the Recordset
rs.Open "SELECT CAMPAIGNID, CONTACTID, CREATEUSER, CREATEDATE, MODIFYUSER, MODIFYDATE, OUTSIDEIMAGE, COPYCONTENT, KEYTAG FROM Q10_CAMPAIGN WHERE CONTACTID= '" & Application.BasicFunctions.CurrentContactID & "'", cn
.....
'Set the field values
rs.Fields("CAMPAIGNID") = "??"
---
End Sub |
|
How did you end up with a PK of CAMPAINID when the Custom table name is Q10_CAMPAIGN?
--
rjl |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 20 Apr 07 9:09 AM
|
| Initially I created the table Q10_CAMPAIGN linked to Contact. I deleted this and craeted the same table linked to Campaign, hence the primary key being CampaignID. Here lies my problem now, because this setup is not correct. CampaignID cannot be the PK because it is not unique. I am a little stumped with what I should make the unique ID, the combination of the contactid and keytag selection is the only defining point. Any suggestions for a PK? I'll go do some reading... |
|
|
|
Re: Basics: Saving edit box to SLXdb
Posted: 22 Apr 07 6:58 PM
|
fiogf49gjkf0d It sounds like you need a new one-to-many table from Contact. Keytag is just another field in this new table - enforce ContactID / Keytag uniqueness through your code. |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|