How to insert some data from Excel spreadsheet and the other data from Access combo boxes

[habinalshaikh]

Hello dears ..

I'm writing a program to add training data to a table named "Dashboard"

some of the training data I import from excel spreadsheet and the other some data I take from the access combo boxes

the table that I named with "Dashboard" contains of :

ID (The primary key)
Project (a combo box feild)
Section (a combo box feild)
Vender (a combo box feild)
Course (a combo box feild)
Employee_ID (I take the data from Excel spreadsheet)
Attendance_Status ( this feild will be based on calculation with another feild)
Attendance_Hourse (I take the data from Excel spreadsheet)
Pre-Assessment (I take the data from Excel spreadsheet)
Post-Assessment (I take the data from Excel spreadsheet)
Cost (I take the data from Excel spreadsheet)


My question is : How can I fill the table with some data I take from an excel spreadsheat and some data I take from combo boxes in the access table same record


I tried fill up the table with Project and the data from the excel spreadsheet by writing the following code :

Private Sub Command12_Click()
Dim strExcelPath As String
strExcelPath = txtPath.Value

Dim numberofparticipant As Integer
Dim SQLstr As String

SQLstr = "INSERT INTO Dashboard (Project) VALUES (" & Project.ListCount & ");"
For numberofparticipant = 1 To 7

DoCmd.SetWarnings False
DoCmd.RunSQL SQLstr
DoCmd.SetWarnings True


Dim FSO As New FileSystemObject
If FSO.FileExists(txtPath.Value) Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Dashboard", strExcelPath, True
End If

Next numberofparticipant


End Sub


But It filled the data (which is from the excel spreadsheet) after that It filled the project (which is from the combo box) in another records

 

[theDBguy]

How do the Excel file and the lookup tables relate? You could try linking to the Excel file manually and simply create a query to get the data you want. You can also convert it to an Appendix query.

 

[habinalshaikh]

thank you for your prompt response

how can i do that ? could you please share a video related to your answer ?

sorry I'm new in Access

thank you again

 

[The_Doc_Man]

First you need to inspect / examine / design / plan (one of those words is right) what you want to do by listing each individual source.

Typically, for things like you describe which sound like you have about a dozen different sources for data that will go into a single record, you need to accumulate all the data first, usually into variables - though you COULD do this directly from the sources if all of the sources are open at once.

Depending on your rules of what goes into this table, you have to validate ahead of time that you have a value for every field in the targeted table. In other words, don't allow someone to store a record if you don't have everything you need to store a complete record.

After you have all the data ready, you dynamically create an SQL string to do an INSERT INTO. The first part of it (the INSERT INTO clause) is easy because that is just a list of field names and is essentially a constant for a given target table.

strInsert = "INSERT INTO target-table (field-name-1, field-name-2,..., field-name-n) "

The second part (the VALUES list) is trickier and this is where you would dynamically build a string containing the values you wanted.

strInsert = strInsert & " VALUES( value-for-field-1, value-for-field-2, ...)"

When building a string dynamically, always either start it or end it with a space so that the string elements are properly separated.

In this scenario, what is more likely is that you would have to individually append isolated strings representing the values for each of your dozen or so fields. Then when the Insert query string is complete, you can execute it to store a single record.

CurrentDB.Execute strInsert, dbFailOnError

Note that what you are describing with that multitude of sources will be unlikely or even impossible to optimize into doing multi-line inserts several records at a time. Based on what you told us, this is something that will only be able to store one record at a time.

I don't keep up with the videos but I'm sure one of my colleagues could point you in that direction. I wanted to give you the overview of where this could go.

 

[Pat Hartman]

My question is : How can I fill the table with some data I take from an excel spreadsheat and some data I take from combo boxes in the access table same record

You can't just make up data. Someone has to decide which combobox record to select for each combo. Does the Excel sheet have some unique indicator that can be used to join to an Access table from which the combobox selections can be obtained?

You would have an Access table with the Excel unique course identifier as the PK. It would then contain the other descriptive values for that course. The excel sheet would have the CourseID, StudentID, date, and hours. You would have an Access table with the same columns. You then just import the excel data. There is no reason to ever copy the data from your course definition table to any other table. When you want to make reports or display the information on forms, you just use the combos. They will automatically link to the correct list value.

 

[habinalshaikh]

You can't just make up data. Someone has to decide which combobox record to select for each combo. Does the Excel sheet have some unique indicator that can be used to join to an Access table from which the combobox selections can be obtained?

You would have an Access table with the Excel unique course identifier as the PK. It would then contain the other descriptive values for that course. The excel sheet would have the CourseID, StudentID, date, and hours. You would have an Access table with the same columns. You then just import the excel data. There is no reason to ever copy the data from your course definition table to any other table. When you want to make reports or display the information on forms, you just use the combos. They will automatically link to the correct list value.

Project (a combo box feild)
Section (a combo box feild)
Vender (a combo box feild)
Course (a combo box feild)

I have tables for these feilds to to help me with the statistics

how many Courses did I conduct
how many Vender deliverd courses
how many project do we have

you find in the attachement the file that i work on

 

[Pat Hartman]

Remove all the table level lookups and review all the responses again. You can't simply make up data out of thin air. Something from the spreadsheet needs to specify how to determine the "lookup values" by pointing to a course.