Make changes to Table Not Open

[Debra Ann]

MS Access 2003

I'm clicking on a command button in a form. When I click on it, it saves
two fields into two variables (one a string and one a variant). I want to do
one of two things:

If variant variable Is Null then
1. add a new record to a two-column table (not opened)
2. type the string variable in the first column (text field)
3. type the variant variable in the second column (long integer)
4. save the file
Else
1. search the first column for the string variable
2. replace the number in the second column with the variant variable + 1
3. save the file
End If

I do not know how to code this or how you can manipulate a table behind the
scenes that is not open.

Your help is greatly appreciated.

thanks,

Debra Ann

 

[Dirk Goldgar]

MS Access 2003

I'm clicking on a command button in a form. When I click on it, it saves
two fields into two variables (one a string and one a variant). I want to
do
one of two things:

If variant variable Is Null then
1. add a new record to a two-column table (not opened)
2. type the string variable in the first column (text field)
3. type the variant variable in the second column (long integer)
4. save the file
Else
1. search the first column for the string variable
2. replace the number in the second column with the variant variable + 1
3. save the file
End If

I do not know how to code this or how you can manipulate a table behind
the
scenes that is not open.

You manipulate a table "behind the scenes" either by executing action
queries (append/update/delete queries), or by using DAO or ADO objects to
work with recordsets that you open on the table. For most purposes,
executing action queries is more efficient.

I can give you example code to do what you describe above, but I don't
understand this:

If variant variable Is Null then [...]
3. type the variant variable in the second column (long integer)

If the variant variable is Null, there's nothing to type in the second
column, so what is it that you want to put there? Do mean that you want to
set that column to Null?

If you'd like some example code, you can help me write it by telling me the
names of the table, the variables, and the fields in the table that you want
to update.

 

[Marshall Barton]

MS Access 2003

I'm clicking on a command button in a form. When I click on it, it saves
two fields into two variables (one a string and one a variant). I want to do
one of two things:

If variant variable Is Null then
1. add a new record to a two-column table (not opened)
2. type the string variable in the first column (text field)
3. type the variant variable in the second column (long integer)
4. save the file
Else
1. search the first column for the string variable
2. replace the number in the second column with the variant variable + 1
3. save the file
End If

I do not know how to code this or how you can manipulate a table behind the
scenes that is not open.

You can do that a couple of ways. I guess that using a
recordset is the easiest to code. Here's some air code
with the general idea of a way to do that:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("twocolumntable",
dbOpenDynaset)

If IsNull(varientvariable) Then
rs.AddNew
!firstfield = stringvariable
' no point to setting second field to Null
rs.Update
Else
rs.FindFirst "firstfield = """ & stringvariable & """"
If Not rs.NoMatch Then
rs.Edit
rs!secondfield = variantvariable + 1
rs.Update
Else
'you didn't say what to do when search fails
? ? ?
End If
End If
rs.Close : Set rs = Nothing

 

[Debra Ann]

Dirk, I decided to go a different route than have two tables and having to
update a different table to keep track of a number system. I have written
the code to help you understand what I am trying to do (forgot what I said
before about update a table not open). The only thing I cannot figure out
now is how to query a table, sort a field in descending order and place the
number in that field into a variable that I will use to populate a field in
my form. Here is the code:

Dim strField1 As String
Dim StrField2 As String
Dim StrField3 As String
Dim StrField4 As String
Dim varLookup As Variant

‘I am in a form and I have clicked a command button to get the next Sequence
Number depending on the uniqueness of Fields 1 through 4

Me.cboField1.SetFocus
strField1 = Me.cboField1.Text
Me.cboField2.SetFocus
StrField2 = Me.cboField2.Text
Me.cboField3.SetFocus
StrField3 = Me.cboField3.Text
Me.cboField4.SetFocus
StrField4 = Me.cboField4.Text

‘This is what I can’t figure out. I want to query the log dependent on the
matchup of the four other fields, sort it descending order, and pick the
number that is in the [Sequence Number] field. The is the sql if I were to
query but not sure how to put it in VB code:

varLookup = "SELECT TOP 1 tbl_LOG.[Sequence Number] FROM tbl_LOG WHERE
(((tbl_LOG.[Field1]) = strField1) And ((tbl_LOG.[Field2]) = StrField2) And
((tbl_LOG.[Field3]) = StrField3) And ((tbl_LOG.[Field4]) = StrField4)) ORDER
BY tbl_LOG.[Sequence Number] DESC;"

‘Fill in the Sequence_Number field on the form

If varLookup Is Null then
Me.Sequence_Number.text = “50001â€
Else
Me.Sequence_Number.text = varLookup + 1
End If

Sorry for the confusion. Any help what the query part would be greatly
appreciated.

MS Access 2003

I'm clicking on a command button in a form. When I click on it, it saves
two fields into two variables (one a string and one a variant). I want to
do
one of two things:

If variant variable Is Null then
1. add a new record to a two-column table (not opened)
2. type the string variable in the first column (text field)
3. type the variant variable in the second column (long integer)
4. save the file
Else
1. search the first column for the string variable
2. replace the number in the second column with the variant variable + 1
3. save the file
End If

I do not know how to code this or how you can manipulate a table behind
the
scenes that is not open.

You manipulate a table "behind the scenes" either by executing action
queries (append/update/delete queries), or by using DAO or ADO objects to
work with recordsets that you open on the table. For most purposes,
executing action queries is more efficient.

I can give you example code to do what you describe above, but I don't
understand this:

If variant variable Is Null then [...]
3. type the variant variable in the second column (long integer)

If the variant variable is Null, there's nothing to type in the second
column, so what is it that you want to put there? Do mean that you want to
set that column to Null?

If you'd like some example code, you can help me write it by telling me the
names of the table, the variables, and the fields in the table that you want
to update.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[Dirk Goldgar]

(Please see my comments interspersed with the quoted message.)

Dirk, I decided to go a different route than have two tables and having to
update a different table to keep track of a number system. I have written
the code to help you understand what I am trying to do (forgot what I said
before about update a table not open). The only thing I cannot figure out
now is how to query a table, sort a field in descending order and place
the
number in that field into a variable that I will use to populate a field
in
my form. Here is the code:

Dim strField1 As String
Dim StrField2 As String
Dim StrField3 As String
Dim StrField4 As String
Dim varLookup As Variant

‘I am in a form and I have clicked a command button to get the next
Sequence
Number depending on the uniqueness of Fields 1 through 4

Me.cboField1.SetFocus
strField1 = Me.cboField1.Text
Me.cboField2.SetFocus
StrField2 = Me.cboField2.Text
Me.cboField3.SetFocus
StrField3 = Me.cboField3.Text
Me.cboField4.SetFocus
StrField4 = Me.cboField4.Text

This code can be improved. In Access, the .Text property of a control has
very limited use, and requires that the control have the focus when you
refer to it. Instead, most code should use the .Value property, which is
the control's default property. Better code for capturing the values of the
combo boxes is:

strField1 = Me.cboField1
StrField2 = Me.cboField2
StrField3 = Me.cboField3
StrField4 = Me.cboField4

‘This is what I can’t figure out. I want to query the log dependent on the
matchup of the four other fields, sort it descending order, and pick the
number that is in the [Sequence Number] field. The is the sql if I were
to
query but not sure how to put it in VB code:

varLookup = "SELECT TOP 1 tbl_LOG.[Sequence Number] FROM tbl_LOG WHERE
(((tbl_LOG.[Field1]) = strField1) And ((tbl_LOG.[Field2]) = StrField2) And
((tbl_LOG.[Field3]) = StrField3) And ((tbl_LOG.[Field4]) = StrField4))
ORDER
BY tbl_LOG.[Sequence Number] DESC;"

‘Fill in the Sequence_Number field on the form

If varLookup Is Null then
Me.Sequence_Number.text = “50001â€
Else
Me.Sequence_Number.text = varLookup + 1
End If

Sorry for the confusion. Any help what the query part would be greatly
appreciated.

What you want here is the maximum sequence number. To get that, you could
open a recordset on the appropriate SQL statement, but for your purpose it's
probably simpler to use the DMax() function:

Const Q As String = """"

varLookup = _
DMax("[Sequence Number]", "tbl_Log", _
"[Field1]=" & Q & strField1 & Q & " AND " & _
"[Field2]=" & Q & StrField2 & Q & " AND " & _
"[Field3]=" & Q & strField3 & Q & " AND " & _
"[Field4]=" & Q & strField4 & Q)

If IsNull(varLookup) Then
Me.Sequence_Number = 50001
Else
Me.Sequence_Number = varLookup + 1
End If

Note that I have assumed that your fields Field1, Field2, Field3, and Field4
are text fields. If they aren't, then the quoting in the Criteria argument
must be adjusted.

 

[Debra Ann]

Worked perfectly. Thank you so very much.

(Please see my comments interspersed with the quoted message.)

Dirk, I decided to go a different route than have two tables and having to
update a different table to keep track of a number system. I have written
the code to help you understand what I am trying to do (forgot what I said
before about update a table not open). The only thing I cannot figure out
now is how to query a table, sort a field in descending order and place
the
number in that field into a variable that I will use to populate a field
in
my form. Here is the code:

Dim strField1 As String
Dim StrField2 As String
Dim StrField3 As String
Dim StrField4 As String
Dim varLookup As Variant

‘I am in a form and I have clicked a command button to get the next
Sequence
Number depending on the uniqueness of Fields 1 through 4

Me.cboField1.SetFocus
strField1 = Me.cboField1.Text
Me.cboField2.SetFocus
StrField2 = Me.cboField2.Text
Me.cboField3.SetFocus
StrField3 = Me.cboField3.Text
Me.cboField4.SetFocus
StrField4 = Me.cboField4.Text

This code can be improved. In Access, the .Text property of a control has
very limited use, and requires that the control have the focus when you
refer to it. Instead, most code should use the .Value property, which is
the control's default property. Better code for capturing the values of the
combo boxes is:

strField1 = Me.cboField1
StrField2 = Me.cboField2
StrField3 = Me.cboField3
StrField4 = Me.cboField4

‘This is what I can’t figure out. I want to query the log dependent on the
matchup of the four other fields, sort it descending order, and pick the
number that is in the [Sequence Number] field. The is the sql if I were
to
query but not sure how to put it in VB code:

varLookup = "SELECT TOP 1 tbl_LOG.[Sequence Number] FROM tbl_LOG WHERE
(((tbl_LOG.[Field1]) = strField1) And ((tbl_LOG.[Field2]) = StrField2) And
((tbl_LOG.[Field3]) = StrField3) And ((tbl_LOG.[Field4]) = StrField4))
ORDER
BY tbl_LOG.[Sequence Number] DESC;"

‘Fill in the Sequence_Number field on the form

If varLookup Is Null then
Me.Sequence_Number.text = “50001â€
Else
Me.Sequence_Number.text = varLookup + 1
End If

Sorry for the confusion. Any help what the query part would be greatly
appreciated.

What you want here is the maximum sequence number. To get that, you could
open a recordset on the appropriate SQL statement, but for your purpose it's
probably simpler to use the DMax() function:

Const Q As String = """"

varLookup = _
DMax("[Sequence Number]", "tbl_Log", _
"[Field1]=" & Q & strField1 & Q & " AND " & _
"[Field2]=" & Q & StrField2 & Q & " AND " & _
"[Field3]=" & Q & strField3 & Q & " AND " & _
"[Field4]=" & Q & strField4 & Q)

If IsNull(varLookup) Then
Me.Sequence_Number = 50001
Else
Me.Sequence_Number = varLookup + 1
End If

Note that I have assumed that your fields Field1, Field2, Field3, and Field4
are text fields. If they aren't, then the quoting in the Criteria argument
must be adjusted.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)