[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 483: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 112: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 112: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/bbcode.php on line 112: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4554: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3751)
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4556: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3751)
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4557: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3751)
[phpBB Debug] PHP Notice: in file /includes/functions.php on line 4558: Cannot modify header information - headers already sent by (output started at /includes/functions.php:3751)
Audio Refugees • View topic - SQL for beginners

SQL for beginners

A place to help the technically inept among us with computer issues, equipment problems, or even website difficulties. If you can offer help, please speak up!

SQL for beginners

Postby Krispy » Sun Feb 01, 2009 9:16 am

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'. :D
To save time, assume I know everything
User avatar
Krispy
 
Posts: 6764
Joined: Wed Apr 23, 2008 10:04 pm
Location: Little Bentcock, Englandshire

Re: SQL for beginners

Postby mistertroll » Sun Feb 01, 2009 9:42 am

Image
Keep your booger hook off the bang switch and nobody gets hurt.

User avatar
mistertroll
 
Posts: 2958
Joined: Thu May 08, 2008 3:58 pm
Location: Dallas!

Re: SQL for beginners

Postby Jay » Sun Feb 01, 2009 10:05 am

Prunes!
User avatar
Jay
 
Posts: 2303
Joined: Wed Apr 23, 2008 10:17 pm

Re: SQL for beginners

Postby Krispy » Sun Feb 01, 2009 10:12 am

To save time, assume I know everything
User avatar
Krispy
 
Posts: 6764
Joined: Wed Apr 23, 2008 10:04 pm
Location: Little Bentcock, Englandshire

Re: SQL for beginners

Postby ROXI » Sun Feb 01, 2009 12:04 pm

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
In a world of Zombies, I'm alone with a match, a beer and a bong, and my heart has turned into stone. Glorify only what you want your child to emulate
User avatar
ROXI
 
Posts: 2809
Joined: Thu Apr 24, 2008 6:09 pm
Location: Minnesota

Re: SQL for beginners

Postby shankarji » Sun Feb 01, 2009 3:13 pm

'kin'ell
A guy at work bought a car out of the paper. Ten years later, Bam! Herpes.Peter Griffin
User avatar
shankarji
 
Posts: 2337
Joined: Thu Apr 24, 2008 8:57 am
Location: southern england

Re: SQL for beginners

Postby Stevo » Sun Feb 01, 2009 5:34 pm

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...
User avatar
Stevo
 
Posts: 1588
Joined: Sat May 03, 2008 4:11 am

Re: SQL for beginners

Postby Krispy » Sun Feb 01, 2009 6:10 pm

To save time, assume I know everything
User avatar
Krispy
 
Posts: 6764
Joined: Wed Apr 23, 2008 10:04 pm
Location: Little Bentcock, Englandshire

Re: SQL for beginners

Postby jaymz » Tue Feb 03, 2009 7:09 am

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
Take that! Hatless!
User avatar
jaymz
 
Posts: 1863
Joined: Wed Apr 23, 2008 9:41 pm
Location: Texas Baby

Re: SQL for beginners

Postby Krispy » Tue Feb 03, 2009 8:31 am

Yeah I know, but that's what I have to work with cos my employer is too cheap to pay for something decent. :roll:
Thanks for the link. :thumb:
To save time, assume I know everything
User avatar
Krispy
 
Posts: 6764
Joined: Wed Apr 23, 2008 10:04 pm
Location: Little Bentcock, Englandshire

Re: SQL for beginners

Postby jaymz » Tue Feb 03, 2009 3:36 pm

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.
Take that! Hatless!
User avatar
jaymz
 
Posts: 1863
Joined: Wed Apr 23, 2008 9:41 pm
Location: Texas Baby

Re: SQL for beginners

Postby Krispy » Wed Feb 04, 2009 9:34 am

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. :bang:
To save time, assume I know everything
User avatar
Krispy
 
Posts: 6764
Joined: Wed Apr 23, 2008 10:04 pm
Location: Little Bentcock, Englandshire

Re: SQL for beginners

Postby jaymz » Wed Feb 04, 2009 3:16 pm

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
Take that! Hatless!
User avatar
jaymz
 
Posts: 1863
Joined: Wed Apr 23, 2008 9:41 pm
Location: Texas Baby

Re: SQL for beginners

Postby Krispy » Fri Feb 06, 2009 4:44 pm

Sorry - meant to say thanks for that, Jaymz - probably get to look at it (the quest) again next week.
To save time, assume I know everything
User avatar
Krispy
 
Posts: 6764
Joined: Wed Apr 23, 2008 10:04 pm
Location: Little Bentcock, Englandshire

Re: SQL for beginners

Postby steban » Thu Feb 04, 2010 7:32 am

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
User avatar
steban
 
Posts: 1661
Joined: Thu Apr 24, 2008 4:47 pm
Location: Magnolia TX

Next

Return to Tech Tools & Advice

Who is online

Users browsing this forum: No registered users and 1 guest

cron