Getting data from access (VB Help)

[Justin]

Hello, need some assistance. I have this access database where it grabs raw
data from an oracle database and user will
upload file onto my database. Now this database will take that raw data and
spit out several excel sheets, depending on
location criteria. Now, these are the fields on the access database
CDI ID
Date
Corp (location)
Account #
ORG
Locator
Subinventory
Box Status
Serial Number
Part #
Description
Operator ID
Date of Upload

Now when the excel sheet is generated, it looks at a template sheet and adds
all data and save it as a different sheet
The fields on the excel sheets are as follows:

Unique Number (CDI ID)
Date (DATE)
Org (ORG)
Sub Inventory (Subinventory)
LOC (Locator)
CVC Part Number (Part #)
Serial Number (Serial Number)
CableDataBox Status (Box Status)
Opr ID (Operator ID)
Corp (CORP)
Account # (Account #)
Coordinator ID
CDI Comments
Coordinator Comments


Now that last three fields are what needs to be filled in. The CDI Comments
is a drop down. There are 5 options:
BARCODE IDENTITY ISSUE
PENDING RESEARCH
LOGISTICS ASSET VALIDATED
NO TROUBLE FOUND
MISSED ISSUES

I need a way to upload the data back to the table, BUT only the last 3
fields. I dont' want to upload data that I already have
What I need is when the user press UPLOAD (there need to be an upload button
on the excel sheet) that it looks at the 3 fields
and if the drop down is blank (default), then that line shouldn't be
uploaded. Only fields that should be uploaded are rows that
the CDI Comments are filled. Now if the CDI COmments are to Logistic Asset
Validated, then it needs to upload that line back to
the table and the delete it self from the excel sheet. If the cdi comments
are any other options, then it uploads but still be there
so user have option to change. When the next time the user press upload and
that row has already been uploaded, msgbox should
appear prompting user if they want to update with new data
Please help. Thanks

 

[SteveS]

Justin,

I'm not sure I understood exactly what you wanted, but maybe this will help you
a little.

The name of the button on the Excel SS should be "Upload".

The first column in the SS should be the "Unique Number" (CDI ID) field.

There are a few places in the code that need to be changed. I marked them with
'<<<<<<<<<<

There is a separate SUB to delete the rows.

WARNING: I did a little testing, but most is air code!!!
----Watch for line wrap!!!---


'********begin code ***************************
Private Sub Upload_Click()

Dim UpdateIt As Boolean

'database variables
Dim D As DAO.Database
Dim R As DAO.Recordset
Dim strSQL As String

'message box variables
Dim Response As Integer
Dim Msg As String
Dim Style As Integer
Dim Title As String

'variables for Excel cells
Dim xCDI_ID As Long
Dim xCDI_Com As String
Dim xCO_ID As Long
Dim xCO_COM As String

Title = "Update New Values?" ' Define title.
Style = vbYesNo + vbQuestion + vbDefaultButton2 ' Define buttons.

' change to the path\name of the database
Set D = "d:\path\name_of_database.mdb" '<<<<<<<<<<<< change this

strSQL = "Select [CDI ID], [Coordinator ID], [CDI Comments], [Coordinator
Comments]"
strSQL = strSQL & " FROM tableName " '<<<<<<<<<<<< change "tableName"
to you table name
strSQL = strSQL & "WHERE [CDI ID] = " & xCDI_ID


'move to the first row with data '(excel sheet)

Range("A2").Select '<<<<<<<<< change if A2 is not the first cell


Do
'get Excel values
xCDI_ID = ActiveCell
xCDI_Com = ActiveCell.Offset(0, 11).Value ' 12th column
xCO_ID = ActiveCell.Offset(0, 12).Value ' 13th column
xCO_COM = ActiveCell.Offset(0, 13).Value ' 14th column


If Not IsNull(xCDI_Com) Then ' 1
'a DAO.recordset with the [CDI ID] as a parameter (WHERE clause)
Set R = D.OpenRecordset(strSQL)
R.MoveLast
R.MoveFirst
'
If R.RecordCount > 0 Then '2
'record found
'by default, update with new data
UpdateIt = True
'Excel cell with "LOGISTICS ASSET VALIDATED" always updated
'database field [CDI Comments] is not null ask to update
If Not IsNull(R.[CDI Comments]) And [xCDI_Com] <> "LOGISTICS ASSET
VALIDATED" Then '3
Msg = "CDI Comments = " & xCDI_Com
Msg = Msg & vbCrLf & "Coordinator Comments = " & xCO_COM
Msg = Msg & vbCrLf & "Coordinator ID = " & xCO_ID
Msg = Msg & vbCrLf & vbCrLf & "Do you want to update with new
data?"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then '4
UpdateIt = False
End If ' 4
End If '3

If UpdateIt Then '5
'update the recordset fields
With R
.Edit
![Coordinator ID] = xCO_ID
![CDI Comments] = xCDI_Com
![Coordinator Comments] = xCO_COM
.Update
End With
End If '5
End If '2
'close recordset
R.Close
End If ' 1
'move to next row
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""

'cleanup database objects
Set R = Nothing
Set D = Nothing

'after all rows uploaded, then
'delete the rows with "LOGISTICS ASSET VALIDATED"
Delete_Rows

End Sub

Sub Delete_Rows()
Dim celladdress As String
Range("A2").Select
Do
If ActiveCell.Offset(0, 11) = "LOGISTICS ASSET VALIDATED" Then
celladd = ActiveCell.Address
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
Range(celladdress).Select
If ActiveCell.Address <> "A2" Then
ActiveCell.Offset(-1, 0).Select
End If
End If
If ActiveCell.Address <> "A2" Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = ""
Range("A2").Select
MsgBox "Upload complete!"
End Sub
'**********end code***************************

BTW, the number after an IF or END IF line helps me to match the IF..END IF pair.



HTH

 

[SteveS]

location criteria. Now, these are the fields on the access database
CDI ID
Date
Corp (location)
Account #
ORG
Locator
Subinventory
Box Status
Serial Number
Part #
Description
Operator ID
Date of Upload

FYI,

"Date" is a reserved word in access and shouldn't be used as an object name.
Also, it is not very descriptive of the data stored in the field: ie Death
date , Hire Date, Add Date, Remove Date, etc.

Using spaces or special characters (#) makes it much harder to work with the
fields in forms, queries and reports. If you want to separate words, use the
underscore ( _ ) or camelback: "DateOfUpload", "AccountNum" or "Account_Num".