|
Subject:
|
Not sure how to phrase this
|
|
Posted By:
|
arholly
|
Post Date:
|
1/18/2007 9:57:13 AM
|
Hello:
I need help again. I'm adding information to a table using the code below, but I need to revise the code to add new functionality. The new functionality I need to add is this.
When someone chooses a session code, I also need it to add the corresponding date into another field, but the users won't see it. For example, if I have a session code of 0701A, I need it to pull the corresponding start_date from "tbl_sessions" and use it to populate the "First_Session" date through the code.
Here's the code I'm using:
Private Sub Command12_Click()
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_Books", dbOpenDynaset)
'all information in here where "Order#" is a column in your table
rst.AddNew
rst("ISBN") = [ISBN]
rst("Book_Title") = [Book_Title]
rst("Publisher") = [Publisher]
rst("Copyright") = [Copyright]
rst("Edition") = [Edition]
rst("Author") = [Author]
rst("Book_Type") = [Book_Type]
rst("Trial_Software") = [Trial_Software]
rst("Comments") = [Comments]
rst("Last_Update") = Now()
rst.Update
rst.Close
If Err = 0 Then
MsgBox "The ISBN number " & ISBN & " was added successfully."
Else
MsgBox "There was an error processing your request."
End If
[ISBN] = Null
[Book_Title] = Null
[Publisher] = Null
[Copyright] = Null
[Edition] = Null
[Author] = Null
[Book_Type] = "Traditional"
[Trial_Software] = "None"
[Comments] = Null
End Sub
Any help would be appreciated.
Best Regards.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/18/2007 11:34:26 AM
|
Where is the Session Code in this code, and which date is the Satart Date?
mmcdonal
|
|
Reply By:
|
arholly
|
Reply Date:
|
1/18/2007 3:38:25 PM
|
Oh Bother. Posted the wrong VBA code. Here is the right code:
Private Sub Command12_Click()
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_Changes_Book_Course", dbOpenDynaset)
'all information in here where "Order#" is a column in your table
rst.AddNew
rst("ISBN") = [ISBN]
rst("Course_ID") = [CourseID]
rst("Reason_Code") = [Reason]
rst("First_Session_Code") = [First_Session_Used]
rst("Last_Review") = [Review_Date]
rst("Last_Update") = Now()
rst("CDPM") = [CDPM]
'rst("Inactive") = [Trial_Software]
rst.Update
rst.Close
If Err = 0 Then
MsgBox "The ISBN number " & ISBN & " was added successfully to " & CourseID
Else
MsgBox "There was an error processing your request."
End If
[ISBN] = Null
[CourseID] = Null
[Reason] = Null
[First_Session_Used] = Null
[Review_Date] = Null
[CDPM] = Null
End Sub
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/19/2007 7:15:53 AM
|
For this answer I am assuming that [First_Session_Used] is a field on your form, and that it gets its data from tbl_sessions, and that the data you are referring to is also in tbl_sessions.
tbl_sessions
sessionID - possibly the session number like 0710A
session_date - the date you want.
Change the combo box that looks up the sessionID to add the session_date in the second or third column.
Then change you code to this:
Private Sub Command12_Click()
Set db = CurrentDb()
Set rst = db.OpenRecordset("tbl_Changes_Book_Course", dbOpenDynaset)
'all information in here where "Order#" is a column in your table
rst.AddNew
rst("ISBN") = [ISBN]
rst("Course_ID") = [CourseID]
rst("Reason_Code") = [Reason]
rst("First_Session_Code") = [First_Session_Used]
rst("Session_Date") = Me.SessionCombo.Column(1)
rst("Last_Review") = [Review_Date]
rst("Last_Update") = Now()
rst("CDPM") = [CDPM]
'rst("Inactive") = [Trial_Software]
rst.Update
rst.Close
If Err = 0 Then
MsgBox "The ISBN number " & ISBN & " was added successfully to " & CourseID
Else
MsgBox "There was an error processing your request."
End If
[ISBN] = Null
[CourseID] = Null
[Reason] = Null
[First_Session_Used] = Null
[Review_Date] = Null
[CDPM] = Null
End Sub
Or the appropriate column. Is that what you wanted?
mmcdonal
|
|
Reply By:
|
arholly
|
Reply Date:
|
1/19/2007 10:01:05 AM
|
Okay, I'm trying to use the code and I've adjusted my combo box accordingly and used the following code, but am still not getting it to add the date to the table. The code is:
rst.AddNew
rst("ISBN") = [ISBN]
rst("Course_ID") = [CourseID]
rst("Reason_Code") = [Reason]
rst("First_Session_Code") = [First_Session_Used]
rst("First_Session") = Me.First_Session_Used.Column(3)
rst("Last_Review") = [Review_Date]
rst("Last_Update") = Now()
rst("CDPM") = [CDPM]
rst.Update
The [First_Session_Used] is being added to the table but the "First_Session" (and I copied the name from the table to make sure it was correct) is not being populated. Any ideas on why it is not?
Thanks in advance.
|
|
Reply By:
|
arholly
|
Reply Date:
|
1/22/2007 1:54:12 PM
|
Any thoughts anyone?
|
|
Reply By:
|
arholly
|
Reply Date:
|
1/25/2007 9:35:28 AM
|
Can anyone help me figure out why the one field of the table is not populating. Thanks.
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/25/2007 10:21:31 AM
|
First of all, make sure that you have the correct column number in your combo box, and that there is a value there.
Secondly, take the value FIRST, then pass it, since passing values from the form at runtime can be problematic:
Dim sFirstSession As String
sFirstSession = Me.First_Session_Used.Column(3)
...
rst("First_Session") = sFirstSession
...
See if that works. On the first one, add this:
MsgBox sFirstSession
to see what value is there. Then remove it from your code after you check.
HTH
mmcdonal
|
|
Reply By:
|
arholly
|
Reply Date:
|
1/25/2007 12:01:05 PM
|
Thanks. Part of the problem was that I apparently did have it pointing to the wrong column. I'm not sure how or why. Does it count columns after the bound column or is the bound column included in the number of columns?
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
1/25/2007 12:20:17 PM
|
The bound column is the default column (hence "bound") and the column() values start after the bound column. I always forget where to start the count, 0 or 1, and just try it a couple times till it gets the right value.
mmcdonal
|
|
Reply By:
|
arholly
|
Reply Date:
|
1/25/2007 12:31:33 PM
|
Thanks. Learning lots about how all this stuff works. I appreciate all the help.
|
