Passing a Combo variable to a Command Button

pgsibson

Registered User.
Local time
Today, 17:33
Joined
Jan 24, 2008
Messages
44
Hi
Thansk to everybodies help over the last few days I have learnt a great deal. After a rethionk I now have reduced a 3 step process to 2. The insertion of a record and the update of that record

Dim dbsCollege As DAO.Database
Dim rstStudentAnnualcourses As DAO.Recordset
Dim intStuID As Integer
Dim strSQL As String
Dim intCrsID As Integer
Dim intLastRecord As Integer

intStuID = Forms!frmStudentCourses!StudID

Set dbsCollege = CurrentDb
Set rstStudentAnnualcourses = dbsCollege.OpenRecordset("tblStudentsAnnualCourses")
strSQL = "INSERT INTO tblStudentsAnnualCourses([StuAnnCrsesStuID]) VALUES (" & intStuID & ")"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
intStuID = 0
'Msg = "Record added Waiting for Course Selection"

Me!LabelSelectaCourse.Visible = True
Me!cboSelectCourse = ""
Me!cboSelectCourse.Visible = True

rstStudentAnnualcourses.MoveLast
intLastRecord = rstStudentAnnualcourses!StuAnnCrsesID
intCrseID = 5
strSQL = ""
strSQL = strSQL & " Update tblStudentsAnnualcourses "
strSQL = strSQL & " Set StuAnnCrsesCrseID = " & intCrseID
strSQL = strSQL & " WHERE StuAnnCrsesID = " & intLastRecord

DoCmd.RunSQL strSQL
intCrseID = 0

'Msg = "Course Added"
'DoCmd.Requery Forms!frmStudentCourses.subfrmCoursesStudent!StuAnnCrsesID
DoCmd.SetWarnings True

is the latest draft. If I explicitly declare intCrseID it works fine but I would like to take it from the cboSelectCourse. How?

Then next steps are the msg boxes and the requery to display the new record in the subform. Msg boxes preferably timed so that they are visual confirmations. How?

All contributions gratefully accepted.

Paul :confused:
 
If cboSelectCourse only contain the ID, you can just set the intCrseID

Code:
   If IsNumeric(cboSelectCourse) = True Then
        intCrseID = cboSelectCourse
   End If

If you have multiple columns in the combobox, you will need to specify which column you want to set to the intCrseID

Code:
    intCrseID = cboSelectCourse.Column(#, cboSelectCourse.ListIndex )
    '# is where the course ID column is.  e.g. 0,1,2,etc....
 
Thanks for that and so quickly :)
 
The second option is the one I need and I am sure it will work except the cboSelectCourse ="" and so the code runs straight through to the run SQL cmd and fails. I need an if then section to wait for a cboSelectCourse entry. How?

Regards
PAul
 
Hi

I have had some advice to break the INSERT and UPDATE elements. The INSERT works perfectly and I now have the UPDATE elements in the after update event of the cboSelectCourse with a loop to detect if the cbo is empty.

I tried your second solution, with # replaced by 1 because I have 3 columns, column 1 is the PK I need but your code breaks because even after selecting a course and hovering over the intCrsID shows it is empty and hovering over the right side of the = shows that it =2009/10 column 2 and the error reported is in the SQL statment because intCrsID is empty

Any ideas?
 
...even after selecting a course and hovering over the intCrsID shows it is empty and hovering over the right side of the = shows that it =2009/10 column 2
You can't check the value of intCrsID until the line AFTER it assigns it. It will not be set until after that line. So if you are checking it while the break is on that line you won't see the right thing - it will still be null.
 
Wow SOS (apt name) that was quick

The code now looks like this
Dim dbsCollege As DAO.Database
Dim rstStudentAnnualCourses As DAO.Recordset
Dim intCrsID As Integer
Dim strSQL As String
Dim intLastRecord As Integer


Me!cboSelectCourse.SetFocus

Set dbsCollege = CurrentDb
Set rstStudentAnnualCourses = dbsCollege.OpenRecordset("tblStudentsAnnualCourses")
intLastRecord = rstStudentAnnualCourses!StuAnnCrsesID
Do Until Not IsNull(Me!cboSelectCourse) And Me!cboSelectCourse <> ""
intCrseID = cboSelectCourse.Column(1, cboSelectCourse.ListIndex)
Loop

strSQL = ""
strSQL = strSQL & " Update tblStudentsAnnualcourses "
strSQL = strSQL & " Set StuAnnCrsesCrseID = " & intCrseID
strSQL = strSQL & " WHERE StuAnnCrsesID = " & intLastRecord

DoCmd.RunSQL strSQL
intCrseID = 0


abd the break is on DoCmd.RunSQL?
 
Sorry, but this part makes no sense:

Do Until Not IsNull(Me!cboSelectCourse) And Me!cboSelectCourse <> ""
intCrseID = cboSelectCourse.Column(1, cboSelectCourse.ListIndex)
Loop


What are you trying for here?
 
Waiting for a selection in the Combo box ?
Repeat of your same answer -

On what event is this code that you have the loop? Is it on a command button, or what? If on a button then you don't need to include that loop and it really makes no sense anyway. I've seen few circumstances which would require that. You can validate whether it has a selection and make the user do it.

And then just change this:

intCrseID = cboSelectCourse.Column(1, cboSelectCourse.ListIndex)

to this

intCrseID = cboSelectCourse.Column(1)
 
I realised as I posted that it was not necessary. The code cam in form a command button but with an after update is not necessary. I will rmove it and try your solution. I tried commenting it out but although there were no reproted errors it did not place intCrseID into the table.
 
Code now

Dim dbsCollege As DAO.Database
Dim rstStudentAnnualCourses As DAO.Recordset
Dim intCrsID As Integer
Dim strSQL As String
Dim intLastRecord As Integer

intCrseID = cboSelectCourse.Column(1)

Set dbsCollege = CurrentDb
Set rstStudentAnnualCourses = dbsCollege.OpenRecordset("tblStudentsAnnualCourses")

rstStudentAnnualCourses.MoveLast
intLastRecord = rstStudentAnnualCourses!StuAnnCrsesID


strSQL = ""
strSQL = strSQL & " Update tblStudentsAnnualcourses "
strSQL = strSQL & " Set StuAnnCrsesCrseID = " & intCrseID
strSQL = strSQL & " WHERE StuAnnCrsesID = " & intLastRecord

DoCmd.RunSQL strSQL
'intCrseID = 0


And there are no reported problems an dthe SQl post a number into the StuAnnCrsesCrsID of 201 instead of th edisplayed 9 there are currently on lt 14 records??
 

Users who are viewing this thread

Back
Top Bottom