Help needed with passing values between forms using OpenArgs

[moke123]

I would really like to see client name and client ID come across..... if you might be so generous to show me the added code that I can use to have both come across I would be extremely grateful for your time!

I only use the ClientID in the openArgs. You don't need to "Send" the client name across. You obtain it in the time form as you'll see below.

heres the code with comments
In the new client form:

Private Sub btoSubmit_Click()


    Me.Dirty = False 'This explicitly saves the new client record


    DoCmd.OpenForm "frmTime", , , , acFormAdd, , Me.ClientID  ' this line opens the time form in data add mode and sets the OpenArgs to the new ClientID.


    DoCmd.Close acForm, Me.Name  ' this line closes the new client form


End Sub

in form frmTime: (note the 2nd line which is not in the example. I present it here just as an option instead of the first line. The first line will dirty the record while the 2nd line wont)

Private Sub Form_Load()


    Me.ClientID = Me.OpenArgs  'this line sets the ClientID to the value of OpenArgs. Note that it will instantiate a new record
    'Me.ClientID.DefaultValue = Me.OpenArgs  'You can use this line instead of the above line and a new record wont be instantiated until you dirty the form


End Sub

the recordsource of frmTime is:

SELECT tblTime.HoursID, tblTime.ClientID, tblTime.StartTime, tblTime.EndTime, tblClients.FName, tblClients.LName, [FName] & " " & [LName] AS ClientName
FROM tblTime INNER JOIN tblClients ON tblTime.ClientID = tblClients.ClientID;

which looks like this in the QBE


You'll notice that the client table is joined to the time table by clientID. the last field in the QBE grid is the first and last name concatenated and aliased as ClientName. You then can use ClientName as a field on the form.

Hope this makes sense to you.

Good Luck

[kelliejean]

thank you so much for the in depth reply moke123, it really helped me and I've got it all set up and sorted! Woo hoo! thanks an absolute million for taking time out to help this absolute newbie, I really appreciate it!!!!! One more problem to get sorted and I'm almost there, yay!

[virgilio]

thanks so much virgilio! I might give this a try at some point - it seems practical - I'm just not sure where you would put the code? Form, click button.......gawd, being a newbie to this stuff is panful! LOL.

If you ever use it, it would look like this,

Private Sub Form_Open(Cancel As Integer)
   Dim StrSql As String

   ' This is your original statement commented out
   'Me.[Client name] = Me.OpenArgs

   'This does the work of that statement
   StrSql = " INSERT INTO YOUR_TABLE_NAME ( THE_FIELD_YOU_WANT_TO_UPDATE ) VALUES (' " & me.OpenArgs & " ' );"
   DoCmd.RunSQL StrSql


End Sub

The SQL is what Access is actually doing behind the scenes when you try to run your first version. I've had a ton of trouble trying to do it your original way, which should have worked btw, see my recent thread on Runtime 2448 errors. Something goes wrong in Access under the hood that makes it intermittently fail. The Udemy classes and tutorials I've taken go straight to SQL strings, and I now think its more reliable too.

[kelliejean]

thanks a million virgilio