FindFirst method finst wrong record

[Mark A. Sam]

A user selecting a Company A for an order er reported that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID] is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times. To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign values to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile, Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set fields to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt Up Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier Frt Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1] <> "" Then
[ordCarrierTime] = DLookup("CarrierTime", "Carriers", "[CarrierName] =
'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs![CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to [ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup("DRemarks", "Customers", "[custID] = " &
Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or [ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , , "[CustID] = " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering Memory Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub

 

[Incorrect Assumption]

Many contributors on this board check all the various
subfora and when they see someone like yourself has taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of bothering
to answer it even once.

-----Original Message-----
A user selecting a Company A for an order er reported that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID] is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times. To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign values to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile, Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set fields to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt Up Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier Frt Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1] <> "" Then
[ordCarrierTime] = DLookup

("CarrierTime", "Carriers", "[CarrierName] =

'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs! [CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to [ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup

("DRemarks", "Customers", "[custID] = " &

Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or [ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , , "[CustID] = " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering Memory Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[Mark A. Sam]

I guess if I understood what you are saying I could respond. Would you
rephrase it? It seems as though you feel I am unjustifyably repeating
myself.

Many contributors on this board check all the various
subfora and when they see someone like yourself has taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of bothering
to answer it even once.

-----Original Message-----
A user selecting a Company A for an order er reported that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID] is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times. To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign values to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile, Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set fields to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt Up Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier Frt Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1] <> "" Then
[ordCarrierTime] = DLookup

("CarrierTime", "Carriers", "[CarrierName] =

'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs! [CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to [ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup

("DRemarks", "Customers", "[custID] = " &

Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or [ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , , "[CustID] = " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering Memory Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[Gary Miller]

I think that was a reference to all of the different groups
you cross-posted the message to. Bad etiquette as multiple
people sometimes give redundant answers and waste their
time. Some don't bother answering blatant cross-posts at
all.

On to your question. Are you saying that this works most of
the time, but there are a couple of times it doesn't? Is it
reproduceable with the same records? If so, that is a clue.
If not, you maybe having memory issues or a corruption
issue. Try importing everything into a new db container if
you are suspecting the corruption.

Gary Miller
Sisters, OR

I guess if I understood what you are saying I could respond. Would you
rephrase it? It seems as though you feel I am unjustifyably repeating
myself.




"Incorrect Assumption"

message news:[email protected]...

Many contributors on this board check all the various
subfora and when they see someone like yourself has taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of bothering
to answer it even once.

-----Original Message-----
A user selecting a Company A for an order er reported that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID] is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times. To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign

values
to form controls.

[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile, Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set

fields
to Zero

[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt

Up
Charges", "[OrdID]

= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier

Frt
Up Charges",

"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1] <>

""
Then

[ordCarrierTime] = DLookup

("CarrierTime", "Carriers", "[CarrierName] =

'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs! [CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to [ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup

("DRemarks", "Customers", "[custID] = " &

Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or [ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , ,

"[CustID]
= " & [ordCustID]

', , acDialog
'Cannot open in Dialog mode without encountering

Memory
Error.

End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[Mark A. Sam]

Hello Gary,

I didn't realize that cross posting is a problem. I do it becuase it gives
me a larger audience. Not everyone frequents every newsgroup.

I have created a new Db and imported the objects many times. I don't doubt
that there is corruption, but that doesn't seem to help any issues that I
have had. Good thought though.

I am having a slew of problems since upsizing to SQL Server (actually I
thought that guy was referring to my many posts about this), inconsistent
and not be able to duplicate them. It doesnt' help that my client wants to
stick with Access2000, but I hadnt had these particular issues until I
changed to SQL Server.

Thanks for the reply.

God Bless,

Mark A. Sam

I think that was a reference to all of the different groups
you cross-posted the message to. Bad etiquette as multiple
people sometimes give redundant answers and waste their
time. Some don't bother answering blatant cross-posts at
all.

On to your question. Are you saying that this works most of
the time, but there are a couple of times it doesn't? Is it
reproduceable with the same records? If so, that is a clue.
If not, you maybe having memory issues or a corruption
issue. Try importing everything into a new db container if
you are suspecting the corruption.

Gary Miller
Sisters, OR

I guess if I understood what you are saying I could respond. Would you
rephrase it? It seems as though you feel I am unjustifyably repeating
myself.




"Incorrect Assumption"

message news:[email protected]...

Many contributors on this board check all the various
subfora and when they see someone like yourself has taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of bothering
to answer it even once.



-----Original Message-----
A user selecting a Company A for an order er reported
that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID]
is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times.
To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server
tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to
the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign values
to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile,
Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set fields
to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in
Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt Up
Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier Frt
Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1] <> ""
Then
[ordCarrierTime] = DLookup
("CarrierTime", "Carriers", "[CarrierName] =
'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good
way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs!
[CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing
default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to
[ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding
DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used
to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup
("DRemarks", "Customers", "[custID] = " &
Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or
[ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on
list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers
Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , , "[CustID]
= " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering Memory
Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[Gary Miller]

Mark,

Don't know what to tell you after what you have tried. Do
you have the latest service packs? Are you using an ADP
project or an .mdb with linked files? Did you use the
SQLServer upsize wizard? I do understand that there are some
real problems with that sometimes although I am not up to
speed on what they are.

Good Luck,

Gary Miller

Hello Gary,

I didn't realize that cross posting is a problem. I do it becuase it gives
me a larger audience. Not everyone frequents every newsgroup.

I have created a new Db and imported the objects many times. I don't doubt
that there is corruption, but that doesn't seem to help any issues that I
have had. Good thought though.

I am having a slew of problems since upsizing to SQL Server (actually I
thought that guy was referring to my many posts about this), inconsistent
and not be able to duplicate them. It doesnt' help that my client wants to
stick with Access2000, but I hadnt had these particular issues until I
changed to SQL Server.

Thanks for the reply.

God Bless,

Mark A. Sam

I think that was a reference to all of the different groups
you cross-posted the message to. Bad etiquette as multiple
people sometimes give redundant answers and waste their
time. Some don't bother answering blatant cross-posts at
all.

On to your question. Are you saying that this works most of
the time, but there are a couple of times it doesn't? Is it
reproduceable with the same records? If so, that is a clue.
If not, you maybe having memory issues or a corruption
issue. Try importing everything into a new db container if
you are suspecting the corruption.

Gary Miller
Sisters, OR

I guess if I understood what you are saying I could respond. Would you
rephrase it? It seems as though you feel I am unjustifyably repeating
myself.




"Incorrect Assumption"

message Many contributors on this board check all the various
subfora and when they see someone like yourself has taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of bothering
to answer it even once.



-----Original Message-----
A user selecting a Company A for an order er reported
that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID]
is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times.
To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server
tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to
the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign values
to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile,
Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set fields
to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in
Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier

Frt
Up

Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup

Carrier
Frt

Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them

to
Zero

If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1]

""
Then
[ordCarrierTime] = DLookup
("CarrierTime", "Carriers", "[CarrierName] =
'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good
way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs!
[CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing
default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to
[ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding
DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used
to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup
("DRemarks", "Customers", "[custID] = " &
Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or
[ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on
list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers
Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , , "[CustID]
= " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering Memory
Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[Mark A. Sam]

Gary,

I am using an .mdb with linked tables. The app is to involved to convert it
to an ADP, especially having no experience with SQL Server. I did use th
upsize wizard. I had a couple of problems, one with out of range dates that
were in the tables due to typing error. I found them all and corrected that
issue. The next was that I couldn't export all of the tables at once, I had
to do them in small batches. But the data seemed to export ok. I had
problems using DAO, but got the ones I could identify resolved by modifying
my code. There are issues coming up now that I simply can't duplicate, and
so I can't resolve them. Its a headache to say the least, especially
becuase I am trying to finish up my pending work and get out of this
business.

God Bless,

Mark

Mark,

Don't know what to tell you after what you have tried. Do
you have the latest service packs? Are you using an ADP
project or an .mdb with linked files? Did you use the
SQLServer upsize wizard? I do understand that there are some
real problems with that sometimes although I am not up to
speed on what they are.

Good Luck,

Gary Miller

Hello Gary,

I didn't realize that cross posting is a problem. I do it becuase it gives
me a larger audience. Not everyone frequents every newsgroup.

I have created a new Db and imported the objects many times. I don't doubt
that there is corruption, but that doesn't seem to help any issues that I
have had. Good thought though.

I am having a slew of problems since upsizing to SQL Server (actually I
thought that guy was referring to my many posts about this), inconsistent
and not be able to duplicate them. It doesnt' help that my client wants to
stick with Access2000, but I hadnt had these particular issues until I
changed to SQL Server.

Thanks for the reply.

God Bless,

Mark A. Sam

I think that was a reference to all of the different groups
you cross-posted the message to. Bad etiquette as multiple
people sometimes give redundant answers and waste their
time. Some don't bother answering blatant cross-posts at
all.

On to your question. Are you saying that this works most of
the time, but there are a couple of times it doesn't? Is it
reproduceable with the same records? If so, that is a clue.
If not, you maybe having memory issues or a corruption
issue. Try importing everything into a new db container if
you are suspecting the corruption.

Gary Miller
Sisters, OR

I guess if I understood what you are saying I could
respond. Would you
rephrase it? It seems as though you feel I am
unjustifyably repeating
myself.




"Incorrect Assumption"
message Many contributors on this board check all the various
subfora and when they see someone like yourself has
taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of
bothering
to answer it even once.



-----Original Message-----
A user selecting a Company A for an order er reported
that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID]
is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times.
To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server
tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to
the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign
values
to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile,
Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set
fields
to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in
Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt
Up
Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier
Frt
Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to
Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1]

""
Then
[ordCarrierTime] = DLookup
("CarrierTime", "Carriers", "[CarrierName] =
'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good
way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs!
[CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing
default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to
[ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding
DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used
to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup
("DRemarks", "Customers", "[custID] = " &
Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or
[ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on
list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers
Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , ,
"[CustID]
= " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering
Memory
Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[Gary Miller]

So, if I am understanding this correctly, the database is
totally a conversion from your original Access DB and not
something that you are linking into from an existing SQL db?
If that is the case, you will probably shake my head at the
next suggestion, even though in the long view it may be a
solution. Recreate all of your tables manually using the
native SQL data types and import all of the data in to the
new version. If there are any problems in the import, at
least they can be singled out.

Something in the back of my mind says that something went
awry in the conversion.

Gary Miller

Gary,

I am using an .mdb with linked tables. The app is to involved to convert it
to an ADP, especially having no experience with SQL Server. I did use th
upsize wizard. I had a couple of problems, one with out of range dates that
were in the tables due to typing error. I found them all and corrected that
issue. The next was that I couldn't export all of the tables at once, I had
to do them in small batches. But the data seemed to export ok. I had
problems using DAO, but got the ones I could identify resolved by modifying
my code. There are issues coming up now that I simply can't duplicate, and
so I can't resolve them. Its a headache to say the least, especially
becuase I am trying to finish up my pending work and get out of this
business.

God Bless,

Mark

Mark,

Don't know what to tell you after what you have tried. Do
you have the latest service packs? Are you using an ADP
project or an .mdb with linked files? Did you use the
SQLServer upsize wizard? I do understand that there are some
real problems with that sometimes although I am not up to
speed on what they are.

Good Luck,

Gary Miller

Hello Gary,

I didn't realize that cross posting is a problem. I

do it
becuase it gives

me a larger audience. Not everyone frequents every newsgroup.

I have created a new Db and imported the objects many times. I don't doubt
that there is corruption, but that doesn't seem to

help
any issues that I

have had. Good thought though.

I am having a slew of problems since upsizing to SQL Server (actually I
thought that guy was referring to my many posts about this), inconsistent
and not be able to duplicate them. It doesnt' help

that
my client wants to

stick with Access2000, but I hadnt had these

particular
issues until I

changed to SQL Server.

Thanks for the reply.

God Bless,

Mark A. Sam



I think that was a reference to all of the different groups
you cross-posted the message to. Bad etiquette as multiple
people sometimes give redundant answers and waste their
time. Some don't bother answering blatant cross-posts at
all.

On to your question. Are you saying that this works

most
of

the time, but there are a couple of times it

doesn't? Is
it

reproduceable with the same records? If so, that is

a
clue.

If not, you maybe having memory issues or a corruption
issue. Try importing everything into a new db

container
if

you are suspecting the corruption.

Gary Miller
Sisters, OR

I guess if I understood what you are saying I could
respond. Would you
rephrase it? It seems as though you feel I am
unjustifyably repeating
myself.




"Incorrect Assumption"
message Many contributors on this board check all the various
subfora and when they see someone like yourself has
taken
the trouble to post a question several times assume
therefore that we are relieved of the burden of
bothering
to answer it even once.



-----Original Message-----
A user selecting a Company A for an order er reported
that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID]
is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple

of
times.

To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server
tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to
the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset,
dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign
values
to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile,
Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set
fields
to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in
Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup

Carrier
Frt

Up
Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier
Frt
Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set

them
to

Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And

rs![Ccarrier1]

""
Then
[ordCarrierTime] = DLookup
("CarrierTime", "Carriers", "[CarrierName] =
'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was

no
good

way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs!
[CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing
default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to
[ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding
DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is

being
used

to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup
("DRemarks", "Customers", "[custID] = " &
Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or
[ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the

"Display
on

list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers
Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , ,
"[CustID]
= " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering
Memory
Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub



.

 

[TC]

I think that was a reference to all of the different groups
you cross-posted the message to. Bad etiquette as multiple
people sometimes give redundant answers and waste their
time. Some don't bother answering blatant cross-posts at
all.

Er, that's the wrong way around!

There is nothing wrong with cross-posting to a small number of relevant
groups. With cross-posting, everyone can instantly see whether anyone else
has answered in the other groups.

The problem is MULTI-posting, where the same questions is posted
*seperately* to different groups. Then, no-one in any of those groups, can
see the answers (if any) that anyone else has posted in the other groups.
MULTI-posting is where you waste your time by answering a question, only to
find later that it was already answered in some other group. This is not a
problem with cross-posting.

Multi-posting is extremely rude. I ocasionally take people up on it, &
sometimes they have the gall to disagree!! (Hey, I'll post what I want, you
can go & get stuffed, etc.) I very seldom use my killfile, but those who
continue to multi-post (after being asked not to) are placed into it,
instantly & permanently!

(snip)

TC

 

[Gary Miller]

TC,

Very good observation on the distinctions of the differences
of the two.

Gary Miller
Sisters, Or

 

[Geoff]

'Procedure to find Customer record and assign

values to the Form from it.

I would suggest you recreate your combo box using
the built-in wizard to find a record on a form. This
will simplify your code.

If you need to know how to do this:

1. Open your form in design view.

2. On the Toolbox toolbar, ensure the "Control
Wizards" button in pushed in (click if necessary).

3. Again, on the Toolbox toolbar, click the combo box
button and click in the form where you want the new
combo box.

4. The wizard starts. In its first dialog, click the
button for "Find a record on my form based on the
value I selected in my combo box".

When you have completed the wizard, you will
need to create some code for the form's OnCurrent
event. Assuming:

1. custID is the table's primary key field (unique
for each customer);

2. custID field is the combo's bound column;

3. txtcustID is a textbox on the form,

the code would be, for example:

Private Sub Form_Current()
Me.Combo10 = Me.txtcustID
End Sub

This code will keep the combo box synchronised
with the form when you change the form's record
other than by using the combo box.

Regards
Geoff

PS - No offence but "God Bless" seems an
inappropriate sign-off to a newsgroup post.
Not everyone shares the same religious views.
Good luck with your project.

 

[Mark A. Sam]

TC,

I guess I am still confused on this issue. I don't see where I have posted
the same question on multiple times. Maybe "Incorrect Assumption" doesn't
understand the difference.

God Bless,

Mark

 

[Mark A. Sam]

Geoff,

" would suggest you recreate your combo box using

the built-in wizard to find a record on a form. "

I didn't know that there as a wizard for the Find Method... I'll check it
out, but don't know how I can test it since I can't reproduce the problem.

" PS - No offence but "God Bless" seems an

inappropriate sign-off to a newsgroup post. "

So would the debate that my response could spark if I responded..lol.

" Not everyone shares the same religious views. "

That's why I don't mention my Faith in Christ Jesus. That would really get
some people in lather.

 

[Mark A. Sam]

So, if I am understanding this correctly, the database is
totally a conversion from your original Access DB and not
something that you are linking into from an existing SQL db?

Yes. Actually the app was in A97 format. My client decided to upgrade to
A2000 this summer. That opened a can of worms, but I got the app working
smoothly. Next he decided to updrade to SQL Server.

If that is the case, you will probably shake my head at the
next suggestion, even though in the long view it may be a
solution. Recreate all of your tables manually using the
native SQL data types and import all of the data in to the
new version. If there are any problems in the import, at
least they can be singled out.

Actually I used the wizard becuase I had tried to use the import/exprt tool
(I forgot the name of it) and it failed. If I recall no data got transfered
or not all of the data. I could try that on the Order and Customer tables,
or at least check out the data types.

Something in the back of my mind says that something went
awry in the conversion.

No doubt something is wrong somewhere.

Thanks Gary.

God Bless,

Mark

 

[Mark A. Sam]

Geoff,

I looked at the Wizard that you suggested. You misunderstand the procedure.
The form is an Order entry form. The combo that is giving the problem
populates the customer field on the order form, so I am pulling data from
another table.

Mark

 

[Geoff]

Mark

You misunderstand the procedure.

No, I did not misunderstand the procedure.
The additional information you now give suggests
that your recordset may benefit from Matthew,
Chapter 7, Verse 7, but it's still not possible to be
definitive on the information to hand.

Anyway, good luck with it.
Geoff

 

[Mark A. Sam]

" Ask, and it shall be given you; seek, and ye shall find; knock, and it
shall be opened unto you: " Matthew 7:7 KJV

"Export and it shall be lost, upsize and it shall go asunder"

 

[Ken Snell]

Mark -

What is the SQL statement for the combo box (which I assume is named
ordCustID) in which the user selects the company? Can you post it?

I'm guessing that your setup of the combo box is causing some erroneous
searches because you have the bound column set as column 1 (not 0), and
you're referring to the value of the combo box in the FindFirst step (which
means it's using the value from column 1, not from column 0).

If you don't want to change the setup of your combo box to have the bound
column be the CustID value, then change the FindFirst step to this:

criteria = "[custID] = " & [ordCustID].Column(0)
--

P.S. I happen to like your signature of "God Bless"!

Ken Snell
<MS ACCESS MVP>

A user selecting a Company A for an order er reported that that Company B
came up instead. The method is:

Dim db As Database
Dim rs As Recordset
Dim criteria As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]
rs.FindFirst criteria

The whole procedure follows below.

The value of [CustID] for Company A = 42
The value of [CustID] for Company B = 12

The items are being selected from a combobox. [CustID] is in Column(0) and
the bound column is 1.
This is a problem that happened only a couple of times. To correct it she
had to close and reopen the app.
This is Access 2000. The Tables are linked SQL Server tables.

Any ideas on a fix are appreciated.

God Bless,

Mark A. Sam


Private Sub ordCustID_AfterUpdate()
On Error GoTo error_Section

'Procedure to find Customer record and assign values to the Form from it.
Dim db As Database
Dim rs As Recordset
Dim criteria As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers", dbOpenDynaset, dbSeeChanges)
criteria = "[custID] = " & [ordCustID]

rs.FindFirst criteria
If Not rs.NoMatch Then 'Record was found so assign values to form controls.
[ordCustName] = rs![Custname]
[ordBillName] = rs![Custname]
[ordBillAddress] = rs![Custadd1]
[ordBillAddress2] = rs![Custadd2]
[ordBillCity] = rs![Custcity]
[ordBillState] = rs![Custstate]
[ordBillZip] = rs![Custzip]
[ordBillCountry] = rs![Custcountry]
[ordBillContact] = rs![CustBillToAttn]
[ordShipName] = rs![Custname]
[ordShipAddress] = rs![Custshipadd1]
[ordShipAddress2] = rs![Custshipadd2]
[ordShipCity] = rs![Custshipcity]
[ordShipState] = rs![Custshipstate]
[ordShipZip] = rs![Custshipzip]
[ordShipCountry] = rs![Custshipcountry]
[ordShipContact] = rs![CustshipToAttn]
[ordTermsRate] = rs![CustTermsRate]
[ordTermsDays] = rs![CustTermsDays]
[ordTermsNet] = rs![CustTermsNet]
[ordSalesrep] = rs![Custsalesrep]
[ordCustPaymentType] = rs![CustPaymentType]

'Set Upcharges First Choice is Customer Profile, Second Choice is Carrier
Methods Table
'If the values in those tables are Null then set fields to Zero
[%Up] = rs![%Up]
[Fixed Up] = rs![Fixed Up]
'if upcharge values are Null then check for values in Carrier Methods
table
If IsNull([%Up]) Then
[%Up] = DLookup("[PercentUp]", "Lookup Carrier Frt Up Charges", "[OrdID]
= " & [OrdID])
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = DLookup("[FixedUp]", "Lookup Carrier Frt Up Charges",
"[OrdID] = " & [OrdID])
End If
'If Upcharge Values are still null then set them to Zero
If IsNull([%Up]) Then
[%Up] = 0
End If
If IsNull([Fixed Up]) Then
[Fixed Up] = 0
End If

[ordSpecialInstructions] = rs![Custcommremarks]
[ordCarrierName1] = rs![Ccarrier1]
[ordCarrierMethod1] = rs![Servtyp1]
If Not IsNull(rs![Ccarrier1]) And rs![Ccarrier1] <> "" Then
[ordCarrierTime] = DLookup("CarrierTime", "Carriers", "[CarrierName] =
'" & rs![Ccarrier1] & "'")
End If
'Rich wanted time brought over, and there was no good way to address
CarrierID so Carriername was
'used which is a Non Duplicate value, so acceptable.
[ordShipAccount1] = rs![Caccount1]
[ordCustFreightPaymentType] = rs![CustFreightPaymentType1]
'[ordShipSpecInst1] = rs![Dremarks] **Not storing default value- Only new
info

[ordSpecialInstructions].SetFocus
'Save this record to prevent UnDo
DoCmd.RunCommand acCmdSaveRecord
End If


'DRemarks is a memo field which gets assigned to [ordShipSpecInst1]. The
rowsource for
'this ordCustID needs to be Select distinct and adding DRemarks doesnt allow
it
'becuase it is a Memo field, so a DLookup is being used to transfer the
field info.

'******************
' [ordShipSpecInst1] = DLookup("DRemarks", "Customers", "[custID] = " &
Me.ActiveControl)

' If IsNull([ordSpecialInstructions]) Or [ordSpecialInstructions] = ""
Then
' [ordSpecialInstructions].SetFocus
' Else
' [ordPO].SetFocus
' End If
'*********************

'This is the only place that will set the "Display on list" (ordCustDisplay)
flag to True
[ordCustDisplay] = False
[ordCustDisplay].Enabled = False



If Not IsNull(DLookup("[Notes]", "Customers Notes", "[CustID] = " &
[ordCustID])) _
And [PopUpCustOENote] = True Then
DoCmd.OpenForm "Customer Profile Notes", , , "[CustID] = " & [ordCustID]
', , acDialog
'Cannot open in Dialog mode without encountering Memory Error.
End If

exit_Section:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Sub

error_Section:
MsgBox Err.Description
Resume exit_Section



End Sub

 

[TC]

Hi Mark

I haven't gone back to check whether your original post was multiposted or
crossposted, so I can not comment on that. But whatever - we all want to do
it right. To anyone else who is reading this thread, just make sure to name
all of the newsgroups (to which you want to send your message), in the
appropriate field of a single message; as opposed to sending the message
several times, to a different newsgroup each time.

Cheers,
TC

 

[Mark A. Sam]

Hello Ken,

What is the SQL statement for the combo box (which I assume is named
ordCustID) in which the user selects the company? Can you post it?

Yes, the combobox is called [ordCustID] as well as the bound field

Here is the SQL statement:

SELECT DISTINCT Customers.Custid, Customers.Custname, Customers.Custadd1,
Customers.Custcity, Customers.Custstate, Customers.Custdisplay,
Customers.Custcode
FROM Customers LEFT JOIN [Customer ST Presses] ON Customers.Custid =
[Customer ST Presses].Custid
WHERE (((Customers.Custdisplay)=Yes))
ORDER BY Customers.Custname;

After looking at that, I don't see any reason for the LeftJoin on [Customer
ST Presses], becuase no fields are being used from that table. I must have
been testing at one time and forgot to remove the table or remove the fields
and neglected to do that. In any event I don't see that as a cuase.

I'm guessing that your setup of the combo box is causing some erroneous
searches because you have the bound column set as column 1 (not 0), and
you're referring to the value of the combo box in the FindFirst step (which
means it's using the value from column 1, not from column 0).

If you don't want to change the setup of your combo box to have the bound
column be the CustID value, then change the FindFirst step to this:

I disagree here. Bound Column 1 is the default on the property sheet which
will point to Column(0) in the query, which is field [custID].

If I set the Bound column on the Combobox to 0, the wrong record (Customer)
will be chosen, for example, if I select Bobst Group, then Fort Orange Paper
Company will be selected.

I created this form over 4 years ago and have never encountered this untill
the switch to SQL Server and it only happened twice. Truthfully I don't
even want to address this issue, but the user will pout about it...lol.

I can't see that my methods are bad. I have been doing this for 10 years,
so maybe I need to change them to conform to SQL Server's engine, I don't
know and don't know where to look.

God Bless,

Mark