Getting info from current record and generating a report 1

[medium]

Using VBA in the past couple of months has definitely proven to be rewarding. However, I have run into a problem on which I need some assistance.

I have a situation where I need to get information from the current record in a form. Then take that information and write it to a table. Does anyone know how to facilitate this using VBA? My example includes taking the date completed from a record in my Work Order Table (the current record displayed in the form) and writing that date to another date field in another table (my triggers table).

Also does anyone know how to use VBA to make a query and a report?

Thanks

 

[Frink]

Hallo,

To refer to a field on the current form, use Me![FieldName]
You only need the []'s if the FieldName contains spaces.

To append a record to a table you can open a recordset and add a record to it, somethin' like:

dim dbDB as database
dim rstTrigger as recordset

set dbDB = CurrentDB
set rstTrigger as dbDB.OpenRecordset("tblTriggers&quot
rstTrigger.AddNew
rstTrigger!datDateFieldNameInTriggerTable = Me!txtDateFieldNameInForm
rstTrigger.Update

You'll probably have to add other fields in the form:
rstTrigger!FieldNameInTriggerTable = whatever

You'll have to consider when you want to do this, but maybe in the AfterUpdate event of the Work Order form, might be a good place. This will try to add multiple Trigger table entries which you may not want. Use rstTrigger.Edit, rather than AddNew to edit the currently selected record in the recordset.
I can show you the code to do this, if you let me know more details of your Trigger table, ie. field names, the Primary key and what links the Trigger table to the Work Order table (ideally would be the primary key, although qould be nothing at all)

- Frink
Hope that helps,

- Frink

 

[medium]

Frink,

Thanks for the post. Very helpful.

medium

 

[medium]

Frink,

Thanks again. However, I'm having trouble figuring out how to implement what you said in the first paragraph about using Me![FieldName]

Here's where my code is so far. Where and how would I implement the Me! ? More comments below the code.

Public Function GetDesc()
Dim strPartNo As String
Dim strPartDesc As String
Dim dtDateCompleted As Date

Dim connPartNo As ADODB.Connection, rstPartNo As ADODB.Recordset
Set connPartNo = Application.CurrentProject.Connection
Set rstPartNo = New ADODB.Recordset

rstPartNpen Source:="WorkOrder", ActiveConnection:=connPartNo, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic

In this, as you can see, I open up the recordset that contains the table which contains the current field in my form. Where exactly would I put the !Me statement. I understand how to write this information to another table once specific information is obtained from the current record in a form. However, I don't know how to obtain information from the current record without having someone enter in the specific record information from a prompt.

Thanks again in advance.

Medium

 

[Frink]

Hallo,

Once you have opened a recordset (and BTW, I think you've missed a
Dim rstPartNo As Recordset
declaration)
you can add or edit records using the either AddNew or Edit methods followed by the Update method on the recordset.
When running code in a form, Me is automatically set up to point to the current form object.
Therefore Me.Name is the name of the form, Me.Visible is whether the form is visible, etc.
To refer to a specific control on the form, use Me!<ControlName>. ie. if you had a control called txtSurname then Me!txtSurname would be the value of the current records data in the txtSurname control.
You can also refer to fields returned in the forms underlying recordsource which may or may not be shown in controls on the form. Do this in the same way. If the recordsource returns a field called strSurname then Me!strSurname will be its value.

Hope this helps,

- Frink