I am entering data through basic macro programming, but I am stuck due to an error that says “Object not set.”. My form contains two forms: one is main (the main form does not connect to any table), and the other is subform, which is connected to my table name, country. I place one combo box in the subform and connect to one field of the table name country (Country_Name) by a SQL (Native) query. As shown in the screenshot, other textboxes and combo boxes are on the main form. I place the push button on the subform, create a macro, and set the macro on the mouse button press event.
My code is:
Sub InsertDataIntoTable(oEvent As Object)
On Error GoTo ErrorHandler
Dim oForm As Object
Dim oConnection As Object
Dim statement As Object
Dim iSQL As String
Dim Airline As String, FlightNo As String, RegNo As String, RegCountry As String, AircraftType As String, seenDate As String, DeptCity As String, ArrCity As String
Dim dateValue As Date
' Get the Main form
oForm = ThisComponent.Drawpage.Forms.getByName("Data_Entry_Form")
' Get Values from the form
Airline = oForm.getByName("cmb_Airline_Select").CurrentValue
FlightNo = oForm.getByName("txt_Flight_No").Text
RegNo = oForm.getByName("txt_Reg_No").Text
AircraftType = oForm.getByName("txt_Aircraft_Type").Text
seenDate = oForm.getByName("df_Aircraft_Seen").Text
DeptCity = oForm.getByName("txt_Dept_City").Text
ArrCity = oForm.getByName("txt_Arr_City").Text
' Convert string date to date type
dateValue = DateValue(seenDate)
' Get the Subform
oForm = ThisComponent.Drawpage.Forms.getByName("Data_Entry_SubForm")
' Get Values from the subform
RegCountry = oForm.getByName("cmb_Country_Reg").CurrentValue
' Get the connection to the Database
oConnection = oForm.ActiveConnection
' Check if connection is valid
If oConnection Is Nothing Then
MsgBox "Database connection not established.", 48, "Error"
Exit Sub
End If
' Prepare SQL Statement for inserting data
iSQL = "INSERT INTO ""tb_Flight_Rec"" (""Airline_Name"", ""Flight_No"", ""Aircraft_RegNo"", ""Reg_Country"", ""Aircraft_Type"", ""Time_Seen"", ""Departure"", ""Arrival"") VALUES ('" & Airline & "', '" & FlightNo & "', '" & RegNo & "', '" & RegCountry & "', '" & AircraftType & "', '" & sateValue & "', '" & DeptCity & "', '" & ArrCity & "')"
' Create Statement and execute the query
oStatement = oConnection.createStatement()
oStatement.executeUpdate(iSQL)
' Close the Statement
oStatement.close()
' Clear Form values
'oForm = ThisComponent.Drawpage.Forms.getByName("Data_Entry_Form")
oForm.getByName("cmb_Airline_Select").Text = "--Select--"
oForm.getByName("txt_Flight_No").Text = ""
oForm.getByName("txt_Reg_No").Text = ""
oForm.getByName("txt_Aircraft_Type").Text = ""
oForm.getByName("df_Aircraft_Seen").Text = ""
oForm.getByName("txt_Dept_City").Text = ""
oForm.getByName("txt_Arr_City").Text = ""
' Clear Sub Form values
'oForm = ThisComponent.Drawpage.Forms.getByName("Data_Entry_SubForm")
oForm.getByName("cmb_Country_Reg").Text = "--Select--"
' Display success message
MsgBox "Data inserted successfully!", 64, "Insert Data"
Exit Sub
ErrorHandler:
MsgBox "An error occurred while inserting data: " & Err.Description, 48, “Error”
Exit Sub
End Sub
Kindly help me to recoginize the problem.
