mrmmickle1
Well-known Member
- Joined
- May 11, 2012
- Messages
- 2,461
So I have an existing Database that has information in it that looks like this. The Chart Number is Unique and will not be repeated:
I have an Excel Spreadsheet that looks like this:
I would like to import records into my database from the Excel Spreadsheet. The Catch is that many of the records in the spreadsheet will have the same Chart Number as a record already in the database.
I would like to go through the data in my spreadsheet one row at a time starting in row 2 and compare the Chart Number in the spreadsheet to all Chart Numbers in the database. If the chart number does not exist I would like to write the record (Plain and Simple). However, if the Chart Number is already present in the database I would only like for Field "Pending_Days" to be updated. This way the user will be able to see any existing notes that are present in the database.
The current report that I process in Excel is processed every morning based off of a database export but it has no way of keeping notes in it because it is done every morning it would wipe out the information from the previous day. By keeping the notes related to the corresponding Chart Number it would save my staff an extreme amount of research and time. This is currently how I am writing records into the Database. Is there a simple way to add the If / Else logic into this Procedure. I have very little experience with MS Access VBA and would be greatly appreciative if someone could help me get this problem ironed out! :
| Excel 2012 | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | |||
| 1 | MR | Chart Number | Clinic Location | Last Name | First Name | Date Received | Sales Representative | Hold Reason | Days Pending | Notes | ||
| 2 | G036373 | OF14-118606 | ABC Clinic | Doe | John | 8/5/2014 | 45445 | 23 | ||||
| 3 | G036342 | OF14-118610 | ABC Clinic | Lowsey | Bill | 8/5/2014 | 45454 | 23 | Notes Here | |||
| 4 | G036344 | OF14-118611 | DEF Clinic | Stone | Matt | 8/5/2014 | 88021 | 23 | ||||
Other | ||||||||||||
I have an Excel Spreadsheet that looks like this:
| Excel 2012 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | MR | Chart Number | Clinic Location | Last Name | First Name | Date Received | Sales Representative | Hold Reason | Days Pending | ||
| 2 | G036373 | OF14-118606 | ABC Clinic | Doe | John | 8/5/2014 | 45445 | 23 | |||
| 3 | G036342 | OF14-118610 | ABC Clinic | Lowsey | Bill | 8/5/2014 | 45454 | 23 | |||
| 4 | G036344 | OF14-118611 | DEF Clinic | Stone | Matt | 8/5/2014 | 88021 | 23 | |||
Other | |||||||||||
I would like to import records into my database from the Excel Spreadsheet. The Catch is that many of the records in the spreadsheet will have the same Chart Number as a record already in the database.
I would like to go through the data in my spreadsheet one row at a time starting in row 2 and compare the Chart Number in the spreadsheet to all Chart Numbers in the database. If the chart number does not exist I would like to write the record (Plain and Simple). However, if the Chart Number is already present in the database I would only like for Field "Pending_Days" to be updated. This way the user will be able to see any existing notes that are present in the database.
The current report that I process in Excel is processed every morning based off of a database export but it has no way of keeping notes in it because it is done every morning it would wipe out the information from the previous day. By keeping the notes related to the corresponding Chart Number it would save my staff an extreme amount of research and time. This is currently how I am writing records into the Database. Is there a simple way to add the If / Else logic into this Procedure. I have very little experience with MS Access VBA and would be greatly appreciative if someone could help me get this problem ironed out! :
Code:
rs.Open "AllSamples", cn, adOpenKeyset, adLockOptimistic, adCmdTable
[COLOR=#008000] ' all records in a table[/COLOR]
r = 2 [COLOR=#008000]' the start row in the worksheet[/COLOR]
[COLOR=#0000ff]Do While[/COLOR] Len(Range("A" & r).Formula) > 0
[COLOR=#008000] ' repeat until first empty cell in column A[/COLOR]
[COLOR=#0000ff]With [/COLOR]rs
.AddNew [COLOR=#008000]' create a new record[/COLOR]
[COLOR=#008000] ' add values to each field in the record[/COLOR]
.Fields("MR_Num") = Range("A" & r).Value
.Fields("Chart_Number") = Range("B" & r).Value
.Fields("Clinic_Location") = Range("C" & r).Value
.Fields("Last_Name") = Range("D" & r).Value
.Fields("First_Name") = Range("E" & r).Value
.Fields("Date_Received") = Range("F" & r).Value
.Fields("Sales_Rep") = Range("G" & r).Value
.Fields("Hold_Reason") = Range("H" & r).Value
.Fields("Pending_Days") = Range("I" & r).Value
[COLOR=#008000] 'There is an additional field entitled "Notes" in the database[/COLOR]
[COLOR=#008000] 'This field is not in the spreadsheet[/COLOR]
[COLOR=#008000] ' add more fields if necessary...[/COLOR]
.Update ' stores the new record
[COLOR=#0000ff] End With[/COLOR]
r = r + 1 [COLOR=#008000]' next row[/COLOR]
[COLOR=#0000ff] Loop[/COLOR]
