Find record using recordsets

[alexboitz]

I'm a complete novice and I've been wrestling with this for weeks.

Combo164 provides a pick list from a Query (Fix Trades) of a table that has
no unique records.
The table fields are Trade, Trade Code, Company Ref
Trade code is unique for each Trade, but there are many Companies and each
Company can have several trades, so there can be 20 or 30, or more, records
with the same Trade and Trade Code, but different Company Ref.
The query is set to show only unique values of the Trade field.
The Combo is used to to select a new trade for a Company, and the correct
field values need to be entered in a new record in the Table.

So Trade=Combo164
The Company Ref is carried over ok by a variable from a previous form.
But I still need to find the correct Trade Code.
Below is my effort using a record set, and Find First,
but it comes up with no match for the Trade.
I used message boxes to check what was happening and made comments of
lines that didn't seem to work or became redundant.

I would be greatly obliged if someone could post the corrected code



Private Sub Combo164_AfterUpdate()
'myCode = [Fix Trades]![Trade Code]
'MsgBox myCode
'myTrade = Combo164
Dim myDBase As DAO.Database

Dim rs As DAO.Recordset, intI As Integer
Dim fld As Field
Set dbsMy = CurrentDb
Set rs = dbsMy.OpenRecordset("Fix Trades")
'setMe.Recordset = Combo164.Recordset
'Set rs = Me.Recordset NO WORK
' check if recordset ok
For Each fld In rs.Fields
Debug.Print fld.Name
Next
' That NO WORK..no visibleeffect.
Dim myCode As String
Dim myRef As Integer
Dim myTrade As String
myTrade = rs!Trade
MsgBox myTrade
'myTrade = CStr(Combo164)
myTrade = Combo164
'myCode = "TEST"
'myCode = rs![Trade Code]
myRef = Text2
' that works to get Company Ref where I can get at it
'DoCmd.FindRecord myTrade, , , acSearchAll, , FindFirst
'myCode = [Fix Trades]![Trade Code]
MsgBox myCode
MsgBox myTrade
rs.FindFirst rs!Trade = myTrade
If rs.NoMatch Then
MsgBox "no record"
End If
'rs.FindFirst "Trade = " & myTrade
' won't accept Mytrade string as valid value though it looks correct
myCode = [Trade Code]
MsgBox myCode
' that was a blank
rs.AddNew
rs!Trade = Combo164
rs![Trade Code] = myCode
' Trade Code field always ends up blank
rs![Company Ref] = myRef
rs.Update
' all worked ok till i tried to get Trade Code
' blank trade codes result in duplicate 'unique records' in the query
End Sub

 

[Ken Sheridan]

The first thing to do is normalize the table. At present, by having both
Trade and Trade Code columns (fields) the table contains redundancy and is
not in Third Normal Form (3NF) so is open to inconsistent data being entered.

If you do not have one already you should create a table Trades with columns
TradeCode and Trade, making the former the primary key. Then fill the table
with an update query like this:

INSERT INTO Trades ([Trade Code], Trade)
SELECT DISTINCT [Trade Code], Trade
FROM YourCurrentTable;

Then normalize your current table by deleting the Trade column in table
design view. Make the Trade Code and Company Ref columns the composite
primary key of this table. Create a relationship between the Trades table
and your current table on the Trade Code columns, ensuring that referential
integrity is enforced.

Back in your form set up your combo box like this:

RowSource: SELECT [Trade Code], Trade FROM Trades ORDER BY Trade;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Before adding any code to the combo box's AfterUpdate event procedure I'd
recommend you change its name to something meaningful like cboTrade rather
than just accepting the default name Access gives it. To insert a row into
the table I'd suggest executing an SQL statement rather than establishing a
recordset. First I'll assume Trade Code and Company Ref are both number data
type columns rather than text, but I'll also give you the alternative code to
use if they are text. In the following I've changed the name of your Text2
control to the more meaningful txtCompanyRef. So the code for the combo
box's AfterUpdate event procedure will be along these lines:


Dim cmd As ADODB.Command
Dim strSQL As String
Dim ctrl As Control

Set ctrl = Me.ActiveControl

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "INSERT INTO [YourCurrentTable]([Trade Code],[CompanyRef]) " & _
"VALUES(" & ctrl & "," & Me.txtCompanyRef & ")"

cmd.CommandText = strSQL
cmd.Execute

If you prefer to use DAO rather than ADO the code would be:

Dim dbs AS DAO.Database
Dim strSQL As String
Dim ctrl As Control

Set dbs = CurrentDb
Set ctrl = Me.ActiveControl

strSQL = "INSERT INTO [YourCurrentTable]([Trade Code],[CompanyRef]) " & _
"VALUES(" & ctrl & "," & Me.txtCompanyRef & ")"

dbs.Execute strSQL

If Trade Code and Company Ref are text data type columns then you'd amend
the code as follows to wrap each value in quotes characters:

strSQL = "INSERT INTO [YourCurrentTable]([Trade Code],[CompanyRef]) " & _
"VALUES(""" & ctrl & """,""" & Me.txtCompanyRef & """)"

In this code each quotes character to be included in the SQL statement is
represented by a pair of contiguous quotes characters. This is how you
include a quotes character in a string which is itself delimited by quotes
characters.

Ken Sheridan
Stafford, England

I'm a complete novice and I've been wrestling with this for weeks.

Combo164 provides a pick list from a Query (Fix Trades) of a table that has
no unique records.
The table fields are Trade, Trade Code, Company Ref
Trade code is unique for each Trade, but there are many Companies and each
Company can have several trades, so there can be 20 or 30, or more, records
with the same Trade and Trade Code, but different Company Ref.
The query is set to show only unique values of the Trade field.
The Combo is used to to select a new trade for a Company, and the correct
field values need to be entered in a new record in the Table.

So Trade=Combo164
The Company Ref is carried over ok by a variable from a previous form.
But I still need to find the correct Trade Code.
Below is my effort using a record set, and Find First,
but it comes up with no match for the Trade.
I used message boxes to check what was happening and made comments of
lines that didn't seem to work or became redundant.

I would be greatly obliged if someone could post the corrected code



Private Sub Combo164_AfterUpdate()
'myCode = [Fix Trades]![Trade Code]
'MsgBox myCode
'myTrade = Combo164
Dim myDBase As DAO.Database

Dim rs As DAO.Recordset, intI As Integer
Dim fld As Field
Set dbsMy = CurrentDb
Set rs = dbsMy.OpenRecordset("Fix Trades")
'setMe.Recordset = Combo164.Recordset
'Set rs = Me.Recordset NO WORK
' check if recordset ok
For Each fld In rs.Fields
Debug.Print fld.Name
Next
' That NO WORK..no visibleeffect.
Dim myCode As String
Dim myRef As Integer
Dim myTrade As String
myTrade = rs!Trade
MsgBox myTrade
'myTrade = CStr(Combo164)
myTrade = Combo164
'myCode = "TEST"
'myCode = rs![Trade Code]
myRef = Text2
' that works to get Company Ref where I can get at it
'DoCmd.FindRecord myTrade, , , acSearchAll, , FindFirst
'myCode = [Fix Trades]![Trade Code]
MsgBox myCode
MsgBox myTrade
rs.FindFirst rs!Trade = myTrade
If rs.NoMatch Then
MsgBox "no record"
End If
'rs.FindFirst "Trade = " & myTrade
' won't accept Mytrade string as valid value though it looks correct
myCode = [Trade Code]
MsgBox myCode
' that was a blank
rs.AddNew
rs!Trade = Combo164
rs![Trade Code] = myCode
' Trade Code field always ends up blank
rs![Company Ref] = myRef
rs.Update
' all worked ok till i tried to get Trade Code
' blank trade codes result in duplicate 'unique records' in the query
End Sub