Passed text doesn't appear until cursor is in Textbox

[AccessQuestion]

So all I am doing is passing data from one from into another.

    DoCmd.OpenForm "Task Details", acNormal, , , acFormAdd
    Forms![Task Details]![Title] = "Work Order"
    Forms![Task Details]![Start Date] = Now()
    Forms![Task Details]![Employee] = Me.AssignedTech
    Forms![Task Details]![Assignment] = Me.Description
    Forms![Task Details]![cmdClose].SetFocus
 
 

Everything works except the Assignment to Description box. When the "Task Details" form opens, all the passed data is there, however the Description box is empty. But if you click within the box the passed Forms![Task Details]![Assignment] text appears. Why is it doing that?? Any help is greatly appreciated.

[MMcCarthy]

The Honest answer? I don't know :)

Let's try a few things.

Open the "Task Details" form in design view. Delete the assignment control then drag it back on to the form from the field list (I'm assuming the controls on this form are bound).

If that doesn't make a difference. Check the data source for both forms and see if the data type is text or memo on both, as in they have the same data type. Also check if the data type is text that the are set to the same number of characters.

Let me know if either of these things makes any difference.

Mary

[NeoPa]

You say "The description box is empty", yet your code assigns the value currently in Me.Description to a control named [Assignment]. Is this the control you're referring to?

What type of control is [Assignment]?

[AccessQuestion]

So I replaced the control for Assignment - no change. Both controls are set to 'Memo'.

NeoPa - I sort of misspoke. The 'Me.Description' is coming from a form named 'Work Orders Status'. I am passing that data to a form named 'Task Detials' where it is reviewed and then placed into the table 'Tasks'Both controls are Text Boxes.

I cannot figure this one out for the life of me.

I suppose I could just submit from Work Orders Status directly into the Tasks table and eliminate the passing of data. I am just not clear on how to insert data into a table with VBA. I know it's 'Insert Into' but my first couple attempts failed. I admittedly gave up. I can give it another go, though.

[Jerry Maiapu]

@AccessQuestion
Hi, if you are not clear on how to insert data from a FORM into a TABLE using VBA, then maybe I can help. Do post your question of what you trying to achieve. An unbound FORM with names of a couple of unbound text boxes and the destination table name and corresponding column names would do...

Now, for your question try and delete this line

Forms![Task Details]![Assignment] = Me.Description

then on the control source of the Assignment txtbox put this;

=Forms![Work Orders Status]![Description]

If it does not work I am just suspecting that one of the object name Description or Assignment is misspelled please check these two names again and verify.


Regards

[AccessQuestion]

Here is a quick video I took to show the issue.

http://www.youtube.com/watch?v=VSGBDQqp4FY

Jerry - I don't think I can change that to =Forms![Work Orders Status]![Description] simply because not only do I pass info to that form, I also enter directly to that form. Does that make sense? Maybe I can do it. Let me try and see if it errors out.

Also, I want to take the data from the 'Work Orders Query' form (the top form) and just place it directly into the Tasks table. Does that help you with helping me with the VBA? :) If a forms control does not have the same name as a field in the table what do you do with the Insert Into?

Thanks for all your help; I truly appreciate it.

[AccessQuestion]

By the way, here is the SQL statement I am trying to use to insert from this form into the Tasks table.

 DoCmd.RunSQL "INSERT INTO Tasks ([Title], [Status], [Start Date], [Employee], [Assignment], [WOID]) VALUES ( 'Work Order', 'Assigned',  Now(), '" & Me.AssignedTech & "',  '" & Me.Description & "', '" & Me.WOID & "');"

This is not working obviously and I have googled by #$@ off. Thanks for any help.

Also, once this is working, how do I prevent that box that Access throws up that says 'You are about to append one record....'

Thanks again.

[AccessQuestion]

Update: Solved. Not the best thing to do I guess, but I just refreshed the form once the data was passed.

Private Sub Command26_Click()
    Me.Status = "Assigned"
    DoCmd.Save
    DoCmd.OpenForm "Task Details", acNormal
    Forms![Task Details]![Title] = "Work Order"
    Forms![Task Details]![Start Date] = Now()
    Forms![Task Details]![Employee] = Me.AssignedTech
    Forms![Task Details]![Assignment] = Me.Description
    Forms![Task Details].Refresh

I would still like to know how to do a proper Insert Into from a form. Thanks for your help.

[Jerry Maiapu]

@AccessQuestion
I'll show you a quicker way of inserting data direct into tables without the SQL insert into clause to avoid your enemy so called "You are about to append one record....'. message

Ok..Assuming that AssignedTech, Description and WOID are text boxes on a form try this:
To insert the records/data put a cmd button or so and select an event of your choice and paste the following

Dim strSQL        As String
  Dim db            As DAO.Database
  Dim rs            As DAO.Recordset
  On Error GoTo ErrorHandler
  Set db = CurrentDb()
  Set rs = db.OpenRecordset("Tasks")
    rs.AddNew ' go to a new record 
    rs!Title = "Work Order"
    rs!Status= "Assigned"
    rs!Start Date = Now()
    rs!Employee = Me.AssignedTech 
    rs!Destination = Me.Description 
    rs!Purpose = Me.WOID 
    rs.Update
 
 MsgBox "Successfully Inserted into table”, vbOKOnly + vbQuestion, "Done"
 
exitHandler:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
 
ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description
      DoCmd.Hourglass False
      Resume exitHandler
  End Select
 
 

If you see the message Successfully Inserted into table then your records must have been already inserted into table, go to table and cross-check. Hereafter, you can delete line #16.; it is only for demo purposes here.

Hope this helps

Regards

Jerry
Karex Mangi

[NeoPa]

I've been monitoring this thread but been too confused to know what to post until the latest set of question.

The usual way to stop the warning messages is to use :

Call DoCmd.SetWarnings(False)

It's good practice to follow this with the reverse when the SQL has been run as otherwise your software is changing the working environment of the operator. EG :

Call DoCmd.SetWarnings(True)

Jerry's suggestion is also a perfectly viable way to handle this situation of course.

When working with SQL from within VBA it may help you to know about the following :

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.