SQL for beginners

[Krispy]

Stevo - Jay - Jaymz...any other supergeeks?

I wonder if you might be good enough to help me with what I'm sure will be a relatively simple piece of VB (in Access )?

The mission is to append selected fields from one record in table B to table C by way of user input [Refnum]. I've got this working in a macro (of queries) attached to a form command button, but would like to write a module, specifically so that the variable (user input) can be used to go to the new record upon completion.
I'm just not sure at what point to use Dim.

Obviously this has nothing whatsover to do with this site, but I thought someone might take pity on me. It's the kind of thing which might take me 3 days to work out all by myself but only ten minutes if I 'phone a friend'.

[mistertroll]

[Jay]

[Krispy]

[ROXI]

Go to end of story first, then tell us about the cracks and bumbs you are having to get their

The Code


Private Sub Form_Load()


Left = (Screen.Width - Width) \ 2
Top = (Screen.Height - Height) \ 2
Combo1.Text = "*.wav"
Combo1.AddItem "*.wav"
Combo1.AddItem "*.mid"
Combo1.AddItem "*.avi;*.mpg"
Combo1.AddItem "All files"


End Sub


Private Sub Combo1_Change()

If ListIndex = 0 Then
File1.Pattern = ("*.wav")
ElseIf ListIndex = 1 Then
File1.Pattern = ("*.mid")
ElseIf ListIndex = 2 Then
File1.Pattern = ("*.avi;*.mpg")
Else
Fiel1.Pattern = ("*.*")
End If

End Sub

¡¡

Private Sub Dir1_Change()

File1.Path = Dir1.Path
If Combo1.ListIndex = 0 Then
File1.Pattern = ("*.wav")
ElseIf Combo1.ListIndex = 1 Then
File1.Pattern = ("*.mid")
ElseIf Combo1.ListIndex = 2 Then
File1.Pattern = ("*.avi;*.mpg")
Else
File1.Pattern = ("*.*")
End If

End Sub
¡¡
Private Sub Drive1_Change()

Dir1.Path = Drive1.Drive

End Sub


Private Sub File1_Click()

If Combo1.ListIndex = 0 Then
File1.Pattern = ("*.wav")
ElseIf Combo1.ListIndex = 1 Then
File1.Pattern = ("*.mid")
ElseIf Combo1.ListIndex = 2 Then
File1.Pattern = ("*.avi;*.mpg")
Else
File1.Pattern = ("*.*")
End If

If Right(File1.Path, 1) <> "\" Then
filenam = File1.Path + "\" + File1.FileName
Else
filenam = File1.Path + File1.FileName
End If
Text1.Text = filenam

End Sub

Private Sub play_Click()

MMPlayer.FileName = Text1.Text
MMPlayer.Command = "Open"
MMPlayer.Command = "Play"
MMPlayer.hWndDisplay = videoscreen.hWnd

End Sub


Private Sub stop_Click()

If MMPlayer.Mode = 524 Then Exit Sub
If MMPlayer.Mode <> 525 Then
MMPlayer.Wait = True
MMPlayer.Command = "Stop"
End If
MMPlayer.Wait = True
MMPlayer.Command = "Close"

End Sub

[shankarji]

'kin'ell

[Stevo]

I could help with writing a sproc to do this take would that user variable as an input parameter, but I'm afraid I know zilch about VBA or macros in Access so can't help you with the sproc call and the sending of that variable via an Access form.

If you want an example sproc, let me know - but it sounds like that isn't the part you need help with, I'm a little unclear...

[Krispy]

[jaymz]

I hate access and never use it. Pretty much limit myself to sharepoint/infopath and vb.net.

Might check out this link:

http://www.blueclaw-db.com/access_datab ... riable.htm

[Krispy]

Yeah I know, but that's what I have to work with cos my employer is too cheap to pay for something decent.
Thanks for the link.

[jaymz]

I was just making excuses for not knowing the answer to your questions.

Ye ole cover up.

Q: "Does anyone know how to..."

A: "ewwww... why are you using?"

Translation: No i don't know how to... but I like to pretend I do.

[Krispy]

I think, being largely self-taught, I can have an over-complicated approach to solving a problem which is actually very simple. If it makes you feel any better, my Access tutor doesn't get what I'm asking either.

[jaymz]

Here is how I would do it in asp.old

dim strUserInput1

validate = true

strUserInput1 = Request.Form("Name of your form field")

' Some validation here

If Request.Form("Name of your form field") = "" Then
Response.Write("You <b>MUST</b> fill in your BLAH before continuing.</p>Click the <b>BACK</b> button on your web browser to fill in this field and try again.</p>")
validate = false
End If

' Here is where it's going to get hairy because I don't know what records you are trying to merge. In this case I am just showing the example
'for updating a table with user input. We could modify the SQL query to bring over a field from Table A to Table B at the same time as well.
'Also, I am using the SQLOLEDB provider, but yours may vary.


'Enter Information into the database
set conn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=SQLOLEDB.1;User ID=YOURUSERNAME;Password=YOURPASSWORD;Persist Security Info=True;Initial Catalog=YOURDATABASENAME;server=YOURSERVERNAME;network library=dbmssocn"
conn.open strConn
set rs2 = Server.CreateObject("ADODB.Recordset")

strSQLQuery = "INSERT INTO dbo.TABLETOBEUPDATED (field1) " & _
"VALUES ('" & strUserInput1 &"'))"
rs2.Open strSQLQuery, conn, 3, 3

set rs2 = Nothing
conn.close
set conn = Nothing

[Krispy]

Sorry - meant to say thanks for that, Jaymz - probably get to look at it (the quest) again next week.

[steban]

Well, jaymz's snippet was pretty good.

I do a lot of VB .Net with Access and SQL, but I'd say I write pretty basic code that gets the job done, even if not always the prettiest.

Say you want to write a module to insert into one table from another table where a refnum is the same in the other table.

Sub AppendRefNum (ByRef parRefNum as String)
'I tend to dim variables at the top of the sub

Dim conn1 As New ADODB.Connection
Dim strSQLQuery1 as String
Dim strSQLQuery2 as String
Dim rs1 as New ADODB.Recordset
dim strCode as String = ""
dim strDept as String = ""
dim str LastSong as String = ""

'establish connection dsnless in code
conn1.Open_
Provider=Microsoft.Jet." & _
"OLEDB.4.0;" & _
Data Source =C:\AccessDBs\myDB.mdb;" & _
"Jet OLEDB:Engine Type=4"

'write queries in a variable
strSQLQuery1 = "Select * from tblB Where RefNum = " & parRefNum

'how you write the specifics depends on your code, this is just an example - I am inbetween vb6 and .Net in my style.
'the below is .Net requires another sub - might could do Set rs1 = conn1.Execute(strSQLQuery) more vb6 style
OpenRecSet(rs1, conn1, strSQLQuery1)

If rs1.BOF = True and rs1.EOF = True then
'no record

Else

rs1.MoveFirst
strCode = rs1.Fields("Code").Value
strDept = rs1.Fields("Dept").Value
strLastSong = rs1.Fields("Last Song").Value
strvaluestring1 = "(Code, Dept, [Last Song]) Values ('" & strCode & "','" & strDept & "','" & strLastSong & "')"
strvaluestring2 = "Code = '" & strCode & "', Dept = '" & strDept & "', [Last Song] = '" & strLastSong & "'"
strSQLQuery1 = "Select * from tblC Where RefNum = " & parRefNum
OpenRecSet(rs2, conn1, strSQLQuery1)

If rs2.BOF = True and rs2.EOF = True then
'no record so insert into
strSQLQuery2 = "Insert Into tblC & strValueString1 & Where RefNum = " & parRefNum
Else
'exists
so update existing with new data
strSQLQuery2 = "Update tblC Set & strValueString2 & Where RefNum = " & parRefNum
End If
conn1.Execute (strSQLquery2)

End If

End Sub