Getting a Value from a currently viewed form

[andycambo]

Hi,

I was wondering if anyone could point me in the right direction on this topic. I currently have a button on a form that views client information from the tblClients table. When this button is pressed, the user is asked to input information and once they've pressed 'okay' the information is entered into a table called tblMatters.

However, what I also want it to do is to take the ClientID from the form where the user pressed the button, so the currently viewed form, and also insert that into tblMatters in the field cID (this is how the tables are linked).

I've had a good search around the net and forums but I can't find anything to help me start this process.

Any ideas, articles, code etc. will be most welcome.

Thanks,
Andy.

 

[wiremonkey22]

Can you attach the db?

 

[andycambo]

Can you attach the db?

I can't really post the database but my tables look like this or will do once everything is working (scaled down version)

tblClientProfile
ClientID - FirstName - Surname - DateOfBith.....
1 - John - Smith - 26/01/1980
2 - Mark - Taylor - 30/07/1975
...............

tblMatters
MatterID - Client ID - DateOfContact - UniqueMatter - UFN.....
1 - 2 - 070809 - 1 - 070809/001
2 - 1 - 070809 - 2 - 070809/002
......................

- is used to show serparation of fields

And the button code is this.

Private Sub NewMatter_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As String
Dim strSQL As String
Dim clientID As Integer
Dim ufnString As Long


'store user input for Date Of Contact
dateOfContact = InputBox("Please Enter the First Date of Contact (000000)")

'Finds the next highest unique matters number and adds 1

intHighestNumber = CInt(Nz(ELookup("[mUniqueMatter]", _
                                    "tblMatters", _
                                    "[mDateOfContact]= '" & dateOfContact & "'", "[mUniqueMatter] DESC"), 0))

intHighestNumber = intHighestNumber + 1

'intHighestNumberString = CStr(intHighestNumber)


'ufnString = dateOfContact & "/" & CStr(intHighestNumber)


'Inserts the above findings into the fields in tblMatters
strSQL = "INSERT INTO tblMatters([mUniqueMatter],[mDateOfContact],[mUFN])" & _
"VALUES (" & intHighestNumber & ", '" & dateOfContact & "', '" & ufnString & "');"

CurrentDb.Execute strSQL, dbFailOnError
 
MsgBox "The new UFN is:" & vbNewLine & dateOfContact & "/" & intHighestNumber, vbOKOnly, "Your New Number"


End Sub

So basically, the user inputs the information of a client (or finds a client if the information has already been inputted (each client has a unique ClientID) via a form. They then press the NewMatter button on the form which asks them for the date of first contact. This code then picks a uniqueMatter number for this date and inputs it into tblMatters. However at the moment there is nothing linking the client to the newly created matter so I need to be able to take the ClientID for the client they are currently viewing on the form and input this into tblMatters along with the other information.

Hope this is enough for you to see what I am trying to achieve.

Thanks
Andy.

 

[ted.martin]

Two ways to do this: 1) is via a SQL append query or 2) using the DAO.Recordset Object. Both will write any data to any table. All you need to do is decide which is easier.

If, as I suspect, that SQL and VBA are not your bag, then you will need to read up on them. For a beginner, I would go for the Recordset option as SQL is not for the faint-heatred; although it would probably be quicker.

 

[NigelShaw]

Hi

if it is the ID of the new record just created, you could use

rs.movelast

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intGetId As Integer 

Set db = CurrentDb
Set rs = Db.OpenRecordset("yourTable", dbOpenDynaset)

With rs
     If Not .BOF or .EOF Then
     .MoveLast

intGetid = ClientID

End With

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

use the "intGetid" to pass the id wherever you want


Hth

Nigel