Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If your status field is text, you need to embed it in quotes.
>>> Set rs = myDB.OpenRecordset("SELECT
Try changing this line to:
>>> Set rs = myDB.OpenRecordset("SELECT
Try changing this line to:
Set rs = myDB.OpenRecordset("SELECT * FROM Status WHERE Code = '" & StatusC & "'")
In addition to what Miriam said, you should create a query that joins your 4 tables. That way you only need to open a single recordset based on the query rather than opening 4 separate recordsets as you have it.
Thakn you mbizup. It works I do not receive the error any more. But dose nto post the result in the combo box.
IrogSinta. What is the different to make it in one Query. I am afraid to be more complicated.
Thank you.
IrogSinta. What is the different to make it in one Query. I am afraid to be more complicated.
Thank you.
>>> But dose nto post the result in the combo box.
That may prove very difficult to resolve without actually seeing a sample database illustrating that issue. There are many possible reasons for this...
- There are no records matching the criteria you are using to look up the value you are trying to place in the combo -- so result of your lookup is null.
- The combo name might me incorrect (double-check that the combo is really named comboStatus).
- There are a variety of design properties that might prevent your combo from displaying data in some situations.
Also, as an aside, this entire block of code:
Can be replaced with a single line:
That may prove very difficult to resolve without actually seeing a sample database illustrating that issue. There are many possible reasons for this...
- There are no records matching the criteria you are using to look up the value you are trying to place in the combo -- so result of your lookup is null.
- The combo name might me incorrect (double-check that the combo is really named comboStatus).
- There are a variety of design properties that might prevent your combo from displaying data in some situations.
Also, as an aside, this entire block of code:
'-------------------------------------------
'List data in the Status field
Set rs = myDB.OpenRecordset("SELECT * FROM Status WHERE Code = " & StatusC)
ComboStatus = rs!Status
'Close and reset the connection
rs.Close
Set rs = Nothing
Can be replaced with a single line:
ComboStatus = DLookup("Status", "Status", "Code = '" & StatusC & "'")
Just to help track down the problem, try adding a couple of message boxes to your code to display values as the code is executing:
Are the values displayed by the message boxes what you are expecting to see?
Msgbox " We are looking up the status for the following code: " & StatusC
Msgbox "This is the status for that code: " & DLookup("Status", "Status", "Code = '" & StatusC & "'")
Are the values displayed by the message boxes what you are expecting to see?
One more note - I realize it is not always possible to respond immediately, but it makes it a lot easier for us to help you resolve your problems if you respond in a timely manner (ideally within the same day). When your feedback is delayed for days at a time, it is easy to lose track of what the issue is and where we left off.
What is the different to make it in one Query. I am afraid to be more complicated.On the contrary, if you add all 4 tables to a query in Design View, then drag the 10 fields you use for the textboxes down to the Field section, then save your query with the name qryTransactions, your code will be shortened to this:
Private Sub ComboSearchT_AfterUpdate()
Dim myDB As Database
Dim rs As DAO.Recordset
' Activite the button
Me!ComUpdateR.Enabled = True
Me!ComDelete.Enabled = True
'Open Connection
Set myDB = CurrentDb()
Set rs = myDB.OpenRecordset("SELECT * FROM qryTransaction WHERE ID = " & ComboSearchT)
'List data in the fields
TextTransaction = rs!Transaction
TextSAC = rs!SignatoryAuthorityCorporate
TextSAR = rs!SignatoryAuthorityRiyadh
TextSAJ = rs!SignatoryAuthorityJeddah
TextRef = rs!Reference
TextED = rs!EffectDate
TextNVD = rs!NotValidDate
TextCategory = rs!Category
TextType = rs!Type
ComboStatus = rs!Status
'Close and reset the connection
rs.Close
Set rs = Nothing
End Sub
If your form is unbound, another possibility is to set your form's recordsource to the query and then bind the textboxes to these fields. You then can add a criteria to the query's ID field to point to Forms!NameOfYourForm!Combo
Private Sub ComboSearchT_AfterUpdate()
' Activite the button
Me!ComUpdateR.Enabled = True
Me!ComDelete.Enabled = True
Me.Requery
End Sub
Thank you very much for your help. I have changed the code to the following code and change the properties of the ComboStatus. It is working now.
Private Sub ComboSearchT_AfterUpdate()
Dim TypeID As Integer
'Declare the Connection
Dim myDB As Database
Dim rs As DAO.Recordset
'Open Connection
Set myDB = CurrentDb()
Set rs = myDB.OpenRecordset("SELECT
' Activite the button
Me!ComUpdateR.Enabled = True
Me!ComDelete.Enabled = True
'List data in the fields
TextTransaction = rs!Transaction
TextSAC = rs!SignatoryAuthorityCorpo
TextSAR = rs!SignatoryAuthorityRiyad
TextSAJ = rs!SignatoryAuthorityJedda
TextRef = rs!Reference
TextED = rs!EffectDate
TextNVD = rs!NotValidDate
TextCategory = DLookup("Category", "Category", "ID = " & rs!IDCategory)
TypeID = DLookup("IDType", "Category", "ID = " & rs!IDCategory)
TextType = DLookup("Type", "Type", "ID = " & TypeID)
ComboStatus = DLookup("Status", "Status", "Code = '" & rs!Status & "'")
'Close and reset the connection
rs.Close
Set rs = Nothing
End Sub
Private Sub ComboSearchT_AfterUpdate()
Dim TypeID As Integer
'Declare the Connection
Dim myDB As Database
Dim rs As DAO.Recordset
'Open Connection
Set myDB = CurrentDb()
Set rs = myDB.OpenRecordset("SELECT
' Activite the button
Me!ComUpdateR.Enabled = True
Me!ComDelete.Enabled = True
'List data in the fields
TextTransaction = rs!Transaction
TextSAC = rs!SignatoryAuthorityCorpo
TextSAR = rs!SignatoryAuthorityRiyad
TextSAJ = rs!SignatoryAuthorityJedda
TextRef = rs!Reference
TextED = rs!EffectDate
TextNVD = rs!NotValidDate
TextCategory = DLookup("Category", "Category", "ID = " & rs!IDCategory)
TypeID = DLookup("IDType", "Category", "ID = " & rs!IDCategory)
TextType = DLookup("Type", "Type", "ID = " & TypeID)
ComboStatus = DLookup("Status", "Status", "Code = '" & rs!Status & "'")
'Close and reset the connection
rs.Close
Set rs = Nothing
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial