Reputation: 174 Stalwart
- Active Posts:
- 629 (0.41 per day)
- 03-February 10
- Profile Views:
- Last Active:
- Apr 18 2014 07:57 AM
- OS Preference:
- Favorite Browser:
- Favorite Processor:
- Favorite Gaming Platform:
- Your Car:
- Dream Kudos:
Posts I've Made
Posted 8 Apr 2014So, form1 initially saves the student record with all empty fields except the autonumber ID. Then form2 is opened and you want to enter the information that will be filled in for the record.
If so then the best way would be to have form1 pass the ID value from the newly created record to form2. Reason being, if there are more than one instances of this app running, selecting max just might get you a different student.
You could create a public property on form2:
Public Property StudentId As Integer
Or you could overload the New constructor and include the value to pass there:
Dim studentId As Integer Public Sub New(ByVal passStudentId As Integer) studentId = passStudentId End Sub
In my opinion and experience-
The advantage to this is that you won't even need the text box change event. I assume (and maybe I am wrong to assume it) that there is a save button (maybe you call it a close button) on form2. That said, in the save button event you would validate the data, then perform the Update using the ID value you passed to form2 which is the primary key of the record anyways. This method ensures that you are editing the correct record and your code is cleaner, easier to read and understand.
Posted 7 Apr 2014I have to admit that I am not exactly sure what this code is trying to accomplish.
The way that I read it is:
When the text box changes value Select the max value from the [student number] field. If the maxStudentNumberValue is null Then set the text box value to a string const 'txtStudent_Number' Else set the text box value to the maxStudentNumberValue + 1 Then even if the previous code causes an error Lets update the database Get the value in the text box (number or text) Attempt to Update Table1 with the text box value (number or text).
Does that sound about right?
Posted 7 Apr 2014Could you please post your current code - you have made some changes and I at least am not sure what state your code is in. Thank you.
Posted 7 Apr 2014Here's what I see with your code.
Private Sub txtStudent_Number_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtStudent_Number.TextChanged
Might want to rethink the placement of this - the text changed event could cause the code to run more often than you want or expect.
Dim stringConn As String Dim OleDbConn As OleDbConnection Dim sqlQuery As String stringConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\StudentProfile.accdb" OleDbConn = New OleDbConnection(stringConn)
Looks good so far - might want to think about scoping the variables to the class level.
Try OleDbConn.Open() sqlQuery = "SELECT MAX([student number]) FROM Table1 " Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn) cmd.ExecuteNonQuery() Dim stringCmd As String = cmd.ExecuteScalar
Here your query statement says you are trying to get the max student number, probably should remove the cmd.ExecuteNonQuery as it is not needed - your execute scalar does what you want.
If stringCmd Is DBNull.Value Then txtStudent_Number.Text = "txtStudent_Number" Else txtStudent_Number.Text = (Convert.ToInt32(stringCmd) + 1).ToString End If Catch myException As Exception MsgBox("Think again!") Finally
If there is no value returned you are setting a text box to the text 'txtStudent_Number' and this should be a number rather than a string value (text) - based on what you are expecting to insert.
Dim DoCmd As Object sqlQuery = "INSERT INTO Table1 ([student number]) VALUES ([@student number]) " DoCmd.OpenForm ("Form2"), WHERE ID:= "ID=" & Me!ID Dim cmd As OleDbCommand = New OleDbCommand(sqlQuery, OleDbConn) cmd.Parameters.AddWithValue("@student number", txtStudent_Number.Text) cmd.ExecuteNonQuery()
Remove the DoCmd items from this block of code and it should be good. Note - as a check you for debugging you can change the following line:
cmd.ExecuteNonQuery() to Dim queryReturnValue As Integer = cmd.ExecuteNonQuery()
The reason being you can then debug and see what value is returned.
QuoteFor UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.
From OleDbCommand.ExecuteNonQuery Method
End Try OleDbConn.Close() End Sub
Posted 7 Apr 2014Because VBA and VB.Net are different platforms. The code that you are looking for to open a form.
Here is the best answer to the difference between VBA and VB.Net, that I could find.
'Create an instance of the Form2 Dim frm2 As New Form2 'Now we show it - causes the form to display. frm2.Show()
- Member Title:
- D.I.C Addict
- Age Unknown
- February 2
- my family, reading, programming,
- Full Name:
- Years Programming:
- Programming Languages:
- VB.NET, C#.NET, ASP.NET, SharePoint 2007 development