Not sure how to phrase this

arholly · January 18th, 2007, 10:57 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:

Code:

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.

mmcdonal · January 18th, 2007, 12:34 PM

Where is the Session Code in this code, and which date is the Satart Date?

mmcdonal

arholly · January 18th, 2007, 04:38 PM

Oh Bother. Posted the wrong VBA code. Here is the right code:

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

mmcdonal · January 19th, 2007, 08:15 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

arholly · January 19th, 2007, 11:01 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:

Code:

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.

arholly · January 22nd, 2007, 02:54 PM

Any thoughts anyone?

arholly · January 25th, 2007, 10:35 AM

Can anyone help me figure out why the one field of the table is not populating. Thanks.

mmcdonal · January 25th, 2007, 11:21 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

arholly · January 25th, 2007, 01:01 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?

mmcdonal · January 25th, 2007, 01:20 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