VB question

[Stang02GT]

Is it possible to code this in VB...

I want to have a button on a search form that once the user finds the record they are looking for has the option to i guess import that record into another table.


I was thinking of maybe a SQL insert statment done in VB....

[Rabbit]

Yes, you can do this with DoCmd.RunSQL "Insert Into ..."

[Stang02GT]

I'm having a really hard time with this SQL code and getting it to work...

i have a table that i need to pull in formation from (Tracker) and id like to copy a record from there into another table SRMisc.

The problem im running in to is that the column names are not the same in each table. In the Tracker table they are just a letter and number.... EX. z6, z7 ,z8, and in the SRMisc table they actually have names like Description, Comments. Another issue is that the Tracker table is a linked table with Ready only privlegages. So i did a create table query called traker ( just left out the C) so i could edit the data, and still cannot get it to work.

what i woud like to do...i would like to search for a record in traker from my form, then when the record is found, hit a button that is going to copy the record into my SRMisc table.

Can i do that since the data names do not match up? (I'm thinking no beucase it keeps giving me all different kinds of errors)

[FishVal]

Can i do that since the data names do not match up? (I'm thinking no beucase it keeps giving me all different kinds of errors)

Hi!

1. INSERT INTO statement needs the type of corresponding fields to match, no matter whether the names match or not.
2. Build a necessary append query in Query Builder, run it to check whether it works properly. Then copy/paste the SQL statement offered by Builder to your code making necessary corrections.

Good luck

[Stang02GT]

thanks for the rather rude reply

[Admin Edit: previous comment has been edited to removed offending statement]

[JKing]

Append queries are always easier when the names matchup however if they do not you can still do it.

Append Queries are in the following format:
[code=sql]
INSERT into table2 (field1, field2, field3, ... fieldn)
SELECT field1, field2, field3, ... fieldn
FROM table1
[/code]
Now in your case the field names don't matchup but that's fine if the data is the same and you list the fields in the proper order. The order in which you list your columns in the INSERT line is the order the corresponding fields should be in the SELECT line.

Here's a little example... We have two tables. tblEmployee has full employee information and the fields empLastName and empFirstName and we've made a new table called tblEmpListing with the fields empLName and empFName.

The names aren't the same but the data is... the append query would look like this:
[code=sql]
INSERT INTO tblEmpListing( empFName, empLName)
SELECT empFirstName, empLastName
FROM tblEmployee
[/code]

Here's what the wrong way would look like:
[code=sql]
INSERT INTO tblEmpListing (empFName, empLName)
SELECT empLastname, empFirstName
FROM tblEmployee
[/code]
Doing it the wrong way we will end up with last names in the first name field and vice versa.

Hope this helps to clarify what you need to do.

[Stang02GT]

Ok i have made an append query and gotten the data into the table where it is supposed to go... here is my sql statement

INSERT INTO SRMisc ( [Tracker Item], Description, Comments, Requestor, [SR Num] )
SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
FROM Tracker
WHERE (((Tracker.Z6)="3918"));

I selected a random record being 3918....how can i put this into VB code and have it append the current record selected on my form by using the Onclick event?

My goal is to have the user pull up a record and be able to hit a button to place the record in the correct table.

[JKing]

Something like this:

[code=vb]
Private Sub Append_Click()

Dim strSQL as String

strSQL = "INSERT INTO table1 (field1, field2) SELECT field1, field2 FROM table2 WHERE table2.ID = " & Me.ID & ";"

Docmd.RunSQL (strSQL)
End Sub
[/code]

Me.ID would be the control that holds the ID for that record.

[FishVal]

Ok i have made an append query and gotten the data into the table where it is supposed to go... here is my sql statement

INSERT INTO SRMisc ( [Tracker Item], Description, Comments, Requestor, [SR Num] )
SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6
FROM Tracker
WHERE (((Tracker.Z6)= "3918"));


I selected a random record being 3918....how can i put this into VB code and have it append the current record selected on my form by using the Onclick event?

My goal is to have the user pull up a record and be able to hit a button to place the record in the correct table.

Insert to OnClick event handler the following code:

    Dim strSQL As String
    
    strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
           "Comments, Requestor, [SR Num] ) " & _
           "SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6 " & _
           "FROM Tracker WHERE Tracker.Z6='" & _
           VariableContainingSearchCriteria & "';"
    DoCmd.RunSQL strSQL

[Stang02GT]

The code works with out generating any errors but it pastes 0 Records


in the VariableContain ingSearchCriter ia & "';"

Do i replace that with the text box where the search criteria is ... so it would me Me.Text19

or do i place the actual data im seraching for Me.Z6


sorry for my ignorance on this subject i am extremely new to VB coding

[Stang02GT]

I Got It To Work!!!!



Thank You Both So Much!!!

[FishVal]

The code works with out generating any errors but it pastes 0 Records


in the VariableContain ingSearchCriter ia & "';"

Do i replace that with the text box where the search criteria is ... so it would me Me.Text19

or do i place the actual data im seraching for Me.Z6


sorry for my ignorance on this subject i am extremely new to VB coding

Sure.
Replace VariableContain ingSearchCriter ia with Me.Text19
To avoid messages like "n records will be added" use DoCmd.SetWarnin gs property.

    Dim strSQL As String
    
    strSQL = "INSERT INTO SRMisc ( [Tracker Item], Description, " & _
           "Comments, Requestor, [SR Num] ) " & _
           "SELECT Tracker.X5, Tracker.Z1, Tracker.Z2, Tracker.Z3, Tracker.Z6 " & _
           "FROM Tracker WHERE Tracker.Z6='" & _
           Me.Text19 & "';"
    With DoCmd
           .SetWarnings=False
           .RunSQL strSQL
           .SetWarnings=True
    End With

[Stang02GT]

One last question...


can i have this look at another feild? like x5?

maybe an OR statement

[JKing]

Yes, you can. But you need to know whether you want an AND or and OR.

An AND will succeed where both conditions are true where as an OR will succeed if at least one condition is true.

[FishVal]

One last question...


can i have this look at another feild? like x5?

maybe an OR statement

Sure. Maybe OR or maybe AND, depends on what do you mean.
Smthng like

strSQL=".............. WHERE Tracker.x6='" & Me.X6 & "' OR Tracker.x5='" & Me.x5 & "';"

Supposed both Tracker.x6 and Tracker.x5 are Text type fields.

The one important thing. If the type of table field is Text you should enclose criteria in single quotation marks ( ' ), if it is Date use #, and use nothing to enclose criteria if it is Number.

Example
WHERE Table.Field = 'text'
WHERE Table.Field = #01/01/01#
WHERE Table.Field = 123

Good luck.