Is there any way when I click on the 'Add Record' button (wizard
generated),
that I can force the form to keep the same 'Under_Account' value WHILE
creating a new record?
The above sounds like a classic one to many. If you use a sub-form, and set
the link master/child fields in the sub-form control, then ms-access will
set the value for you, and you don't have to write one line of code.
However, it sounds like your setup launches the 2nd form, and thus YOUR CODE
must set the foreign key field in this case.
There is several solution, but the simple one would be use the forms
before-insert event. the code would be
me!Under_Account = forms!MainFormName!Account_No
So, you only do need one line of code. I used the before insert event, since
then if the user add a new record, but decides they do NOT want the record,
then no code runs, the Under_Accont field is NOT set until the user actually
types, or does something to that record. However, the instant the user
starts to type (dirty) the record, then the before insert code fires. This
way, possible code, or lookups that MIGHT need to use the Under_Account
field will be able to even before the reocrd is acctualy commited to disk
(saved).
2. How do I edit Database fields DIRECTLY thru VBA Code?
The above really depends on what you want to do. Are you talking about the
current record in a form, or a bunch of records in a table?
For example, lets write code to change all occurrences of a city field =
"N.Y" to New York in code.
dim strSql as string
strSql = "update tblCustomers set City = 'New York' where City = 'N.Y.' "
currentdb.execute strSql
The above would thus modify our field data. So, executing sql commands is a
very common approach. You could also write old style code to loop through
what we call a record set, and the RESULTS WOULD BE IDENTICAL to the above:
Dim strSql As String
Dim rstRecords As dao.Recordset
strSql = "select * from tblCustomers"
Set rstRecords = currenddb
Do While rstRecords.EOF = False
If rstRecords!City = "N.Y." Then
rstRecords.Edit
rstRecords!City = "New York"
rstRecords.Update
End If
rstRecords.MoveNext
Loop
rstRecords.Close
Set rstRecords = Nothing
Of course, we could modify the above, and use sql to restrict the records
BEFORE we loop
hence
strSql = "select * from tblCustomers where City = 'N.Y.' "
Set rstRecords = currenddb
Do While rstRecords.EOF = False
rstRecords.Edit
rstRecords!City = "New York"
rstRecords.Update
rstRecords.MoveNext
Loop
rstRecords.Close
Set rstRecords = Nothing
note how the above does not need to test for city, since we restricted the
data to that in our sql statement.
However, all 3 of the above examples does the same thing, and the sql
example is a LOT less code!! In fact, my examples are poor, since you don't
need reocrdsets in the above if you use sql.
