Set a Macro Update or Change Dbase Definition?

[Rich_Lovina]

In one related table, SECTION, I have an IT toggle yes/no according to the text descript. New records add weekly in here & new IT=yes's are manually made (not enough yet to make a macro).

In my table,MAIN, there's another IT=yes determined by several criteria, one of which is SECTION.

Where in the MAIN dbase definition could I write an Iif SECTION!IT=yes, update MAIN!IT to yes?

 

[jwindon]

Rich: Heard about the 100 queries. So I hate to say this, but an update query will do what you want on this. I'm looking into doing the same thing, so I hope there is some way to do it in code. I've posted in the VBA module to see if someone looks there and would know.

 

[jwindon]

Rich: Came across this, I think this may apply...would have to change the !FirstName etc. to Forms!frmFormName![Control]


Update Method Example

This example demonstrates the Update method in conjunction with Edit method.

Sub UpdateX()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")

With rstEmployees
.Edit
' Store original data.
strOldFirst = !FirstName
strOldLast = !LastName
' Change data in edit buffer.
!FirstName = "Linda"
!LastName = "Kobara"

' Show contents of buffer and get user input.
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
!FirstName & " " & !LastName & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"

If MsgBox(strMessage, vbYesNo) = vbYes Then
.Update
Else
.CancelUpdate
End If

' Show the resulting data.
MsgBox "Data in recordset = " & !FirstName & " " & _
!LastName

' Restore original data because this is a demonstration.
If Not (strOldFirst = !FirstName And _
strOldLast = !LastName) Then
.Edit
!FirstName = strOldFirst
!LastName = strOldLast
.Update
End If

.Close
End With

dbsNorthwind.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

Sub UpdateX2()

Dim dbsNorthwind As Database
Dim rstEmployees As Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")

With rstEmployees
.AddNew
!FirstName = "Bill"
!LastName = "Sornsin"

' Show contents of buffer and get user input.
strMessage = "AddNew in progress:" & vbCr & _
" Data in buffer = " & !FirstName & " " & _
!LastName & vbCr & vbCr & _
"Use Update to save buffer to recordset?"

If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
.Update
' Go to the new record and show the resulting data.
.Bookmark = .LastModified
MsgBox "Data in recordset = " & !FirstName & _
" " & !LastName
' Delete new data because this is a demonstration.
.Delete
Else
.CancelUpdate
MsgBox "No new record added."
End If

.Close
End With

dbsNorthwind.Close

End Sub

 

[Rich_Lovina]

Tks, but at this stage I still don't know how to plug in a Module and want to master as much from within Access 1st before looking at VB Code.

1. The queries still show up in main window even after added to Group1. Spose that doesn't really matter, just makes window_Grp1 clearer to work from.
2. Have another one:

Table_1stnames has 5,000 records, 2nd fld is Salute (Mr/Ms only).

I'm trying to write a query that looks up Barry and places "Mr" in the temp downloading table.
This didn't work, as UPDATE, or SELECT put in the criteria line.

IIf([1STNAMES]![1stname]=[WKTOPUPB]![First Name],[1STNAMES]![Salute]," ")

I fear I've put something in the wrong place.
Any ideas appreciated, folks.

 

[Rich_Lovina]

Tks any way, I just solved the last problem as well by running two queries for Ms and Mr, after running an append of new firstnames in the macro.
Tks all, I'm virtually there with 40 action queries in the one macro....phew