Open a form to new record with data pre-filled (1 Viewer)

[Swatkins]

Okay, first let me describe what I'm trying to do, then how I'm going about it and where it's failing.

I've got one form ("F_NewActivity") where I create an id for an activity record. When I save and close this form, I want to automatically open the "F_TimeLog" form, create a new TimeLog record and automatically fill in the associated ActivityID from the just-created Activity record in the TimeLog. From a user perspective, I want the user to see the associated ActivityName for the selected ActivityID.

So here's what I'm doing:

At the end of the "save and close" button click event on the NewActivity form I have the following code:

    iActID = Me.ActivityID
    
    'Debug.Print "Activity ID = ? " & iActID
    
    DoCmd.Close acForm, "F_NewActivity", acSaveNo 'This is because the record is saved through another means, and I don't want it to resave here.
    
    DoCmd.OpenForm "F_TimeLog", , , , acFormAdd, , "ActivityID|" & iActID

This is using the OpenArgs for the OpenForm command.

So, in the Form_Open event of the form "F_TimeLog", I have the following code:

    Dim strActID As String
    Dim x As Variant
    Dim strCtl As String
    Dim iActID As Integer
    
    strActID = Me.OpenArgs
    
    If Len(strActID) > 0 Then
        x = Split(strActID, "|")
        strCtl = x(0)
        iActID = x(1)
        
        Me(strCtl) = iActID '<---Runtime Error 2448 occurs here
    
    End If

strCtl refers to the control name "ActivityID", which is currently a combobox on the TimeLog form. I have this control hidden from view.

I then have a text box where the associated ActivityName is supposed to print. There is code in the Form_Load event which I'd intended to pull the ActivityID from the hidden combobox, find the associated ActivityName, and fill that in on the textbox. That code is here:

Dim iActivityID As Integer
Dim strActivityName As String
Dim sql As String

Dim db As Database
Dim rst As Recordset

    iActivityID = Me.ActivityID
    sql = "SELECT [T_Projects&Activities].ActivityName FROM [T_Projects&Activities] WHERE ([T_Projects&Activities].ActivityID=" & _
        iActivityID & ");"
    Set db = CurrentDb
    Set rst = db.OpenRecordset(sql)
    
    strActivityName = rst!ActivityName
    Me.txt_Activity = strActivityName

What happens is that I get Runtime Error '2448': "You can't assign a value to this object" at the marked line of code in the second block of code (in the Form_Open event), so I don't think it's ever getting to the third block of code (the Form_Load event).

Any ideas (1) about what's going wrong here or (2) a better way to achieve the desired results?

Thanks immensely!

 

[pbaldy]

You can try the load event; I think the open event is too soon. You can also send the value:

DoCmd.OpenForm "F_TimeLog", , , , acFormAdd
Forms!F_TimeLog.ActivityID = iActID

 

[Swatkins]

That looks like an elegant solution. Let me give that a try.

 

[Swatkins]

You can try the load event; I think the open event is too soon. You can also send the value:

DoCmd.OpenForm "F_TimeLog", , , , acFormAdd
Forms!F_TimeLog.ActivityID = iActID

So, this fills in the ActivityID on the TimeLog form. So far, so good.

But I can't find at what point the ActivityID is considered loaded into the form so that I can run my query on ActivityID and pull the ActivityName.

I get nothing if I try to run the query in the Current event for the form, as well as nothing when running it in the AfterUpdate event for ActivityID. In both cases it's an empty value. (Debug.print tells me the ActivityID in both cases is 0, although I do see the correct ActivityID on the form when it opens.)

Any further thoughts?

 

[pbaldy]

My presumption was you'd add other required info and save the record. At the points you've described, it hasn't been saved yet.

 

[Swatkins]

My presumption was you'd add other required info and save the record. At the points you've described, it hasn't been saved yet.

True that it hasn't been saved it yet. I wanted to have the ActivityName auto-filled for the user before they add any additional data to the TimeLog and save it.

 

[pbaldy]

Isn't that happening? You can force a save, but it would seem a waste until the rest of the info is entered.

 

[Swatkins]

True that it hasn't been saved it yet. I wanted to have the ActivityName auto-filled for the user before they add any additional data to the TimeLog and save it.

Let me throw out another idea I had but couldn't figure out how to make work. If I stored the ActivityID in a combobox, I could easily assign the ActivityName to a second column and display that instead. My problem there was aesthetic: I didn't want to see the drop down. In fact, I don't want the activity/activityID to be editable at all by the user at this point, so these are disabled textboxes for display only.

If I could figure out how to make a plain textbox into 2 columns, then I could accomplish the same thing without having a grayed-out dropdown button.

 

[Swatkins]

Isn't that happening? You can force a save, but it would seem a waste until the rest of the info is entered.

Yeah, i haven't figured out how to get the activityName to properly populate along with with ActivityID. Right now I have them in separate textboxes, and after the ID textbox is filled in my thought was to look up the Name and fill that in, but so far that bit of code never executes (not in any of the events I've tried using).

 

[pbaldy]

A textbox can't hold 2 columns, but you could have a hidden textbox bound to the ID field and a visible textbox that you place the text value in for the user.

 

[Swatkins]

A textbox can't hold 2 columns, but you could have a hidden textbox bound to the ID field and a visible textbox that you place the text value in for the user.

I was trying something like that with code to update the second textbox based on the value in the first. Just can't get it to work.


BTW.... thanks for the "Forms!F_TimeLog.ActivityID = iActID" code idea. Don't know why I honestly hadn't thought of that.

 

[Swatkins]

A textbox can't hold 2 columns, but you could have a hidden textbox bound to the ID field and a visible textbox that you place the text value in for the user.

Is there another way to link the value of the 2nd textbox to the value in the first?

 

[pbaldy]

You can simply add a line:

Forms!F_TimeLog.ActivityID = iActID
Forms!F_TimeLog.UnboundTextbox= TextValue

If the text value isn't available on the first form, you can use a DLookup() to get it. If the user is picking the activity from a combo on the first form, the text value should (or can) be available by referring to the appropriate column of the combo. Like:

http://www.baldyweb.com/Autofill.htm

 

[pbaldy]

Is there another way to link the value of the 2nd textbox to the value in the first?

Since the first would be hidden anyway, it could be a combo and you could use the method from the link to display the text value.

 

[Swatkins]

You can simply add a line:

Forms!F_TimeLog.ActivityID = iActID
Forms!F_TimeLog.UnboundTextbox= TextValue

If the text value isn't available on the first form, you can use a DLookup() to get it. If the user is picking the activity from a combo on the first form, the text value should (or can) be available by referring to the appropriate column of the combo. Like:

http://www.baldyweb.com/Autofill.htm

Oh my gawd, that should've be so damn obvious. Thank you!

 

[pbaldy]

Oh my gawd, that should've be so damn obvious. Thank you!

Happy to help!