frount end back end vba help needed

[dinkytrouble]

Hi
I have a problem where i have split my database to FEBE. I can query my tables and see the information i want, apart from my coding to transfer my data to a new table. would some one please have a look at it and tell me where i am going wrong and possibly help me to change the code so it would work. My VBA skills is very limited as you can see.
i have put in a note to say where it halts as i think the problem lies in the fact the tables are linked and i need to tell it that. many thanks
mike

Private Sub Command35_Click()
Dim LResponse As Integer

LResponse = MsgBox("Do You Want To Release This Lot To Blister Loading ?", vbYesNo, "Continue")

If LResponse = vbYes Then


MSG = "Lot Released !!" ' and take appropriate
Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, TEMP9, TEMP10 As String


temp2 = [CAVITY]
temp3 = [pldcavity]
temp4 = [label Power]
temp5 = [MAIN pld]
temp7 = [passes]
temp8 = [oven]
TEMP9 = [fit]


halts here>
Dim MyDB As Database, MyTable As Recordset
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("BLISTER LOADING", DB_OPEN_TABLE)
On Error Resume Next ' Set up error handler.
MyTable.AddNew

MyTable("CAVITY") = temp2
MyTable("PLDCAVITY") = temp3
MyTable("target POWER") = temp4
MyTable("main pld") = temp5
MyTable("starts") = temp7
MyTable("oven") = temp8
MyTable("FIT") = TEMP9

MyTable.Update
MsgBox "released"
DoCmd.Close

Else
MsgBox "This Lot Has Not Been Released To Blister Loading !!", , "Status Report.."

End If

End Sub

 

[randy700]

Why not just run a Make Table Query?

Private Sub [COLOR=blue]cmdNewTable[/color]_Click()
   Dim strSQL As String
   strSQL = "SELECT Cavity, PLDCavity, [target POWER], [main pld], starts, oven, FIT "
   strSQL = strSQL & "FROM [BLISTER LOADING] "
   strSQL = strSQL & "INTO [COLOR=blue][i]NewTableName[/i][/color]"
   DoCmd.SetWarnings False
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings True
End Sub

Randy

 

[dhookom]

You can't open a linked table using DB_OPEN_TABLE. You can try change this to dbOpenDynaset.

      Dim MyDB As DAO.Database, MyTable As DAO.Recordset
      Set MyDB = DBEngine.Workspaces(0).Databases(0)
      Set MyTable = MyDB.OpenRecordset("BLISTER LOADING", dbOpenDynaset)
      On Error Resume Next    ' Set up error handler.

Another option would be to set MyDB to your table MDB file using something like:

      Set MyDB = OpenDatabase("F:\folder\MyTables.MDB")

You can generally pull the path to the linked MDB by grabbing the name from the connection property of a linked table.


Duane
Hook'D on Access
MS Access MVP

 

[JoeAtWork]

(Randy - I don't see anything in the OP's requirements that he needs to create a new table - he is just adding records to an existing one.)

I tend to use ADO instead of DAO. Although DAO is supposed to be more efficient with Access MDB files, I find ADO more flexible and easier to work with.

After making a reference to Microsoft ActiveX Data Objects (if you are on Access2003 or greater you probably already have that reference), a little tweaking of you code should do it:

Private Sub Command35_Click()
Dim LResponse As Integer

LResponse = MsgBox("Do You Want To Release This Lot To Blister Loading ?", vbYesNo, "Continue")

If LResponse = vbYes Then

   
        MSG = "Lot Released !!"  ' and take appropriate
        Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, TEMP9, TEMP10 As String

      
      temp2 = [CAVITY]
      temp3 = [pldcavity]
      temp4 = [label Power]
      temp5 = [MAIN pld]
      temp7 = [passes]
      temp8 = [oven]
      TEMP9 = [fit]

      Dim MyTable As ADODB.Recordset

      Set MyTable = New ADODB.Recordset

      '[COLOR=red]Your "error handler" doesn't "handle" anything at all - you are telling it if there is an error to just ignore it and keep going.  That is MUCH WORSE then not having any error handler at all.  This is because your users will never be able to report what error you are getting - they can only tell you "it doesn't work".  Even worse, they may not even realise there is a problem, and meanwhile all your data is not being saved.[/color]
      'On Error Resume Next    ' Set up error handler.

      On Error Goto RealErrorHandler

      MyTable.Open "[BLISTER LOADING]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

      MyTable.AddNew
      
      MyTable("CAVITY") = temp2
      MyTable("PLDCAVITY") = temp3
      MyTable("target POWER") = temp4
      MyTable("main pld") = temp5
      MyTable("starts") = temp7
      MyTable("oven") = temp8
      MyTable("FIT") = TEMP9

      MyTable.Update

      MsgBox "released"
      DoCmd.Close
       
    Else
        MsgBox "This Lot Has Not Been Released To Blister Loading !!", , "Status Report.."

    End If

Exit Sub:

    Exit Sub
    
RealErrorHandler:
    MsgBox "Error # " & Err.Number & " occurred, description: " & Err.Description, vbExclamation, "Error"

End Sub

 

[TheAceMan1]

How are ya dinkytrouble . . .

Although others have presented solutions here, I'm going to stick with your learning process ... what it appears your really interested in! Scanning your code I come up with:
[ol][li]All [blue]Dim[/blue} statements go at the beginning of the subroutine/function, like so:

[blue]   Dim LResponse As Integer
   Dim MyDB As Database, MyTable As Recordset
   Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, TEMP9, TEMP10 As String[/blue]

Why this way! ... if you ever have to check your [blue]Dim[/blue] statements for any reason (spelling or whatever), you'll know exactly where to go ... instead of parsing thru a long sub/function routine to find it.[/li]
[li]Never ... Ever ... prescribe names you assign with spaces in them. No spaces means no necessary brackets ... making things harder to read ... let alone more trouble down the road![/li]
[li]You can directly intergrate your initial [blue]MsgBox[/blue] statement with your initial [blue]If[/blue] statement:

[blue]   If MsgBox("Do You Want To Release This Lot To Blister Loading ?", vbYesNo, "Continue") = vbYes Then[/blue]

[/li]
[li]Next in your logic flow you have a Msg (I believe it was ment to be a MsgBox). In any case its in the wrong position as far as your logic flow is considered ... [blue]nothings been saved to the table as yet![/blue] You'll this fit in its proper place later ...[/li]
[li]Your biggest problem ... and [red]the reason the code failed[/red], is that string data requires [blue]single/double quotations[/blue] for proper syntax! Like so:

temp2 = [CAVITY]       should be > temp2 = [RED][B][I]"[/I][/B][/red]BLCAVITY[RED][B][I]"[/I][/B][/red]                  
temp3 = [pldcavity]    should be > temp3 = [RED][B][I]"[/I][/B][/red]pldcavity[RED][B][I]"[/I][/B][/red]
temp4 = [label Power]  should be > temp4 = [RED][B][I]"[/I][/B][/red]label Power[RED][B][I]"[/I][/B][/red]
temp7 = [passes]       should be > temp7 = [RED][B][I]"[/I][/B][/red]passes[RED][B][I]"[/I][/B][/red]
temp8 = [oven]         should be > temp8 = [RED][B][I]"[/I][/B][/red]oven[RED][B][I]"[/I][/B][/red]
TEMP9 = [fit]          should be > temp9 = [RED][B][I]"[/I][/B][/red]fit[RED][B][I]"[/I][/B][/red]

Note that [blue]temp2 = [CAVITY][/blue] assigns the value of the current record under CAVITY to temp2.
Where as temp2 = [RED]"[/red]BLCAVITY[RED]"[/red] assigns the fieldname as a string! ...[/li]
[li]As for the rest of the code I'll just modify ... you should be able to follow. Current modified code should be:

[blue]   Dim LResponse As Integer
   Dim MyDB As Database, MyTable As Recordset
   Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, TEMP9, TEMP10 As String
   
   temp2 = "CAVITY"
   temp3 = "pldcavity"
   temp4 = "label Power"
   temp5 = "MAIN pld"
   temp7 = "passes"
   temp8 = "oven"
   TEMP9 = "fit"

   If MsgBox("Do You Want To Release This Lot To Blister Loading ?", vbYesNo, "Continue") = vbYes Then
      Set MyDB = DBEngine.Workspaces(0).Databases(0)
      Set MyTable = MyDB.OpenRecordset("BLISTER LOADING", DB_OPEN_TABLE)
      
      On Error Resume Next    ' Set up error handler.
      
      MyTable.AddNew
         MyTable(temp2) = Me!temp2
         MyTable(temp3) = Me!temp3
         MyTable("target POWER") = Me!temp4
         MyTable(temp5) = Me!temp5
         MyTable("starts") = Me!temp7
         MyTable("oven") = Me!temp8
         MyTable("FIT") = Me!TEMP9
      MyTable.Update
      
      MsgBox "released"
      DoCmd.Close
      Msg = "Lot Released !!"  ' and take appropriate
   Else
      MsgBox "This Lot Has Not Been Released To Blister Loading !!", , "Status Report.."
      
   End If[/blue]

[/li][/ol]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Unless I read this wrong this is a simple FE/BE set up using just Access. So I have to ask, besides for academic purposes why are we doing any of this? If your vb skills are limited, why would you choose an unbound solution. Why not Just bind the form to your table?. To me the solutions should go in this priority.
1. Simple bound form
2. Append query from a temp table bound to the form
3. Append query using the form controls values as variables
4. DAO/ADO recordset.

A recordset solution would be by far my last choice.

Sometimes people will say they have to have an unbound form to keep a record from getting created and they want a "Save" "Cancel" button. You can do this simply on a bound form where cancel is tied to the form_before update event. And you just undo the record. Way easier and more efficient.

There is a time and place for unbound forms, but I have built 100s of applications and use them very infrequently. I have found frequently on TekTips people asking for help on very complicated unbound forms, and I can give them a solution far easier that is bound.

 

[dhookom]

Another comment

   Dim MyDB As Database, MyTable As Recordset

should probably be

   Dim MyDB As DAO.Database, MyTable As DAO.Recordset

And

   Dim temp1, temp2, temp3, temp4, temp5, temp6, temp7, temp8, TEMP9, TEMP10 As String

Creates only TEMP10 as a string. All of the others are variants.


Duane
Hook'D on Access
MS Access MVP