Scan button on form insert file destination in table (1 Viewer)

[tomtom]

Hi,

I am quite new in VB and am quite ashamed to ask this actually but it is keeping me busy for two weeks now. Any help would be most appreciated!!! thanks in advance!

Private Sub Command69_Click()

Dim sFile As String
Dim mydevice As WIA.Device
Dim item As WIA.item
Dim imfile As WIA.ImageFile
Dim Commondialog1 As WIA.CommonDialog
Dim myDB As Database

Set myDB = OpenDatabase("C:\Documents and Settings\Tom\Documents\5_Personallia\project1.accdb")

sFile = ("C:\Documents and Settings\Tom\Documents\5_Personallia\fotoos\Photo1.jpg")
sFile = IncrementFileName(sFile)
myDB.Execute "Insert into Factuur (PictureLink3) VALUES ('" & sFile & "')"

Set Commondialog1 = New CommonDialog
Set imfile = Commondialog1.ShowAcquireImage(, , , wiaFormatJPEG)

If imfile Is Nothing Then
   MsgBox "Action aborted"
Else
   imfile.SaveFile (sFile)
End If

Exit_Command0_Click_click:
Set mydevice = Nothing
Set item = Nothing
Exit Sub

Err_Command0_Click_click:
MsgBox Err.Description, vbOKOnly + vbCritical, "Error Taking Picture"
Resume Exit_Command0_Click_click

End Sub

I have a form with a button that when it is pressed it automatically scans an image (code is from bits and pieces of the net) to a specific folder. When the image is stored the directory of that image is stored in a table named "Factuur" and column named "PictureLink3" . Now everything works fine except that the image directory is always appended at the end of the column and not in the field of the current record the form is showing...
I tried the update statement as well but it says there is no record selected...

 

[MarkK]

This SQL inserts a new record.

INSERT INTO Factuur 
  ( PictureLink3 ) 
VALUES 
  ( <someFilespec> )

To update a specific record do something like...

UPDATE Factuur
SET PictureLink = <someValue>
WHERE RecordID = <someID>

So the specific record is identified by RecordID and the single field is updated without creating a new record.
Cheers,
Mark

 

[tomtom]

Dear Mark,
thank you for your reply!
The <someID> is what I have problems with. I know I can use me.currentrecord
but when I do that or declare a variable for that it says it is 0 whereas the record ID certainly isn't...
So what wold the <someID> be of the current record selected on the form?
thx
bw
Tom

 

[MarkK]

So what wold the <someID> be of the current record selected on the form?

Only you can know this. It depends on how you named your data and/or your controls on the form. But you provide a hint here when you say ...

....whereas the record ID certainly isn't...

... so presumably you know the value of the record ID. What control do you look at to know? Or what field do you look at? And it is that value you want to use.
If that value is stored in a control on the form, you might use SQL like ...

myDB.Execute _
  "UPDATE Factuur " & _
  "SET PictureLink = '" & Me.PathToPhoto & "' " & _
  "WHERE RecordID = " & Me.RecordID

Is that helping?
Mark

 

[tomtom]

Hi Mark!!
thank you very much!! that did it
Can't believe this was soo simple.... have been looking to far for this one...looks like my head is not in it at the moment...
Much much appreciated! thank you again!

have a nice Christmas and New Year!!
enjoy!

 

[MarkK]

You too -> happy holidays.