By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,389 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

how can I have a value show on a form based on a query

P: 37
I have a form it has several text boxes, fields and a subform. it has a parameter that prompts for a SerialNbr.
The fields on the form are SerialNbr, cboCartStatus, CartDate, CartID, ServiceID
Subform fields: cboCartEvent, CartID, CartEventSubType, CartEventDate

The control source for the form is a query that uses the table tbl_Cart. the fields in the tbl_Cart are: CartID, SerialNbr, ServiceID, CartStatus, CartStatusDate
and the tbl_Cart is related to the tbl_Services and its fields are: ServiceID, AddressID, ServiceStatus, NbrCarts, etc

The AddressID comes from a view which is the basis for the entire application. So a veiw was created based on the Services table and the vAddress view this is where the vService_Address comes from. It returns all matching records that has an address that's related to a service.

Hence, what the form does is...when the form is opened it asks the user for a SerialNbr. ONce the serial nbr is entered it brings up the record that is associated with that SerialNbr.


The following pseudocode was given to me saying this is how I need to do it. Can someone help me turn this pseudocode in to VBA code.


On the Cart Form, insert an Address label under the “Cart Update” title.
In the Form Load subroutine, set the default caption to “Address Unknown”.
Query the Cart table using the Serial Number as the parameter.
If the query returns a record, check the ServiceID.
If it is not null, then query view vService_Address using the ServiceID as the parameter.
Set the Address label to the concatenated address returned by the query
Nov 3 '11 #1

✓ answered by Seth Schrock

Here is the code that I put into the Form OnLoad event:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(ServiceID) Then
  2.  
  3.     If IsNull(DLookup("StreetAddress", "qryCombineAddress", "ServiceID=" & ServiceID)) Then
  4.         Me.lblAddress.Caption = "Address Unknown"
  5.     Else
  6.         Me.lblAddress.Caption = DLookup("StreetAddress", "qryCombineAddress", "ServiceID=" & ServiceID)
  7.     End If
  8. End If
This tests to make sure that a record was returned when the form was opened and then does the test of whether to use address unknown or to put the address.

Share this Question
Share on Google+
31 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
I'm not exactly sure what you are trying to do, but it sounds like you might be wanting the DLookup() function. Here is an example that you would put in the AfterUpdate event for the SerialNbr control:
Expand|Select|Wrap|Line Numbers
  1. Me.ServiceID = DLookup("ServiceID", "tbl_Cart", "SerialNbr =" & Me.SerialNbr)
You would then have a line for each control that needs populated.

I hope that I have helped some. If you have any further questions, just ask.
Nov 4 '11 #2

P: 37
thank for your reply...

I am just basically trying to have the address appear on the form for whatever record is displaying. and the pseudocode I posted is what i wanna do to accomplish this. but not sure how to go about doing it.
Nov 4 '11 #3

Seth Schrock
Expert 2.5K+
P: 2,951
Can you attach a copy of your database? I'm not being able to figure out what the pseudocode is trying to. Having the database will help.
Nov 4 '11 #4

P: 37
ok it's attached to a sql backend so let me make the tables local and I will try to attach it.
Nov 4 '11 #5

P: 37
I don't see where it allow me to attach anything
Nov 4 '11 #6

P: 37
nevermind...I found it
Nov 4 '11 #7

Seth Schrock
Expert 2.5K+
P: 2,951
You will have to compress the file into a zip folder to attach it FYI.
Nov 4 '11 #8

P: 37
can't get the db small enough to attach.
Nov 4 '11 #9

P: 37
is it possible that I can email it to you?
Nov 4 '11 #10

Seth Schrock
Expert 2.5K+
P: 2,951
Okay, can you copy the forms that are involved into another database and then attach that? I'll create the tables based on the information give in your OP.
Nov 4 '11 #11

Seth Schrock
Expert 2.5K+
P: 2,951
That would work. {email address removed}
Nov 4 '11 #12

P: 37
here is the db with the forms and query.
Attached Files
File Type: zip Database.zip (23.9 KB, 47 views)
Nov 4 '11 #13

Seth Schrock
Expert 2.5K+
P: 2,951
Well, I can't get the database that you sent me to work because of missing tables, but at least I have the form design. Let me see if I'm understanding your question now. Do you type in the serial number in the SerialNbr box on the Cart Update form and then you want to have it pull the Status, CartStatusDate, etc. or are you entering a number in the Text51 control right under the Cart Update label?
Nov 4 '11 #14

P: 37
I am entering the num in the serial nbr box. the other text box is where I want the adress to be. it probably needs to be a label.
Nov 5 '11 #15

Seth Schrock
Expert 2.5K+
P: 2,951
I would leave Text51 as a textbox. You can do more things with it.

See if this is what you are looking for: try putting a combo box in the form and use the wizard to "Find a record on my form based on the value I selected in my combo box." In Access 2010, it is the third option.
Nov 5 '11 #16

P: 37
if any way possible, I'd like to do it this way...

In the Form Load subroutine, set the default caption to “Address Unknown”.
Query the Cart table using the Serial Number as the parameter.
If the query returns a record, check the ServiceID.
If it is not null, then query view vService_Address using the ServiceID as the parameter.
Set the Address label to the concatenated address returned by the query

but I'm not sure how to write the actual code to do each of these lines.
Nov 7 '11 #17

Seth Schrock
Expert 2.5K+
P: 2,951
Well, I can only give you a sketch of what I think might work, but it will be hard to be very accurate without being able to see the database.

For the first line of the pseudocode:
Expand|Select|Wrap|Line Numbers
  1. If IsNull({address field}) Then
  2. Me.txtAddress = "Address Unknown"
  3. Else
  4. {continue to the rest of the code})
  5. End If
The code in the "Else" portion above does the last line of the pseudocode.

The second line just requires setting up a query with the serial number of the current record set as the criteria.

Third line and Forth Line:
Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(DLookup("ServiceID", "qryCheckForServiceID")) Then
  2. Me.Address = DLookup("AddressField", "vService_Address")
  3. End If
I'm not sure if this makes sense and I have no way of testing it, so make sure to ask if you have any questions about what I've given you.
Nov 8 '11 #18

P: 37
to query the cart table is that a select statement like this?

Select * from tbl_Cart Where SerialNbr = ?
Nov 8 '11 #19

P: 37
i tried to attach the db but it is too large even after I removed all the tables, queries, forms that is not used. Is there any way I can email you the db?
Nov 8 '11 #20

Seth Schrock
Expert 2.5K+
P: 2,951
Your query is correct. The criteria would be
Expand|Select|Wrap|Line Numbers
  1. WHERE SerialNbr = Forms!{form name}!{control Name}
For the database, can you delete most of the records? I'm guessing that if it is too big to upload here, it will be too big for an email. I'll send you a PM with my email so that it isn't available to everyone.
Nov 8 '11 #21

Seth Schrock
Expert 2.5K+
P: 2,951
It seems that everything works except for the address field under the "Cart Update". From what you emailed me, your code for that field (Text51) references a table/query that doesn't exist called vAddress. At first I thought that it was supposed to be vAddresses after the table, but there is no field titled vServiceAddress in the vAddresses table. So I then tried the Address field from the vService_Address table which does work. However, it comes back with just 4023. If you want to have it include the street name as well, then I would create a query such as the following and then do the dlookup on it (shown in the second code box).
Expand|Select|Wrap|Line Numbers
  1. SELECT Address & (' '+Unit) & (' '+Street_Name) & (' '+Street_Type) AS StreetAddress
  2. FROM vService_Address
  3. WHERE (((vService_Address.ServiceID)=[Forms]![Cart Update]![txtServiceID]));
  4.  
I named this query "qryCombineAddress"

Now for your code in Text51, it is up to you if you want to test if just the address field of the vService_Address is null or if you want to test to see if the combined address from qryCombineAddress is blank. I'll show you both options.
Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNull(DLookUp("Address","vService_Address","ServiceID = " & [txtServiceID])),
  2. "Address Unknown",
  3. DLookUp("StreetAddress","qryCombineAddress"))
The above code tests just the address field to see if the street number (such as the 4023 result previously). The code below tests for the whole address field being blank.

Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNull(DLookUp("StreetAddress","qryCombineAddress")),
  2. "Address Unknown",
  3. DLookUp("StreetAddress","qryCombineAddress"))
Let me know if that is what you are looking for.
Nov 9 '11 #22

P: 37
I had the text box there because that was the way someone on another forum suggested it do it, I meant to remove that before I sent it to you. I tried it this way with the code you gave but it doesn't display the address when I enter the serial number. it displays it only if the form is displaying the first record.

ok that's the right idea. But is there any way to kinda take the code you have come up with and have it do it this way:

On the Cart Form, insert an Address label under the “Cart Update” title.
In the Form Load subroutine, set the default caption to “Address Unknown”.
Query the Cart table using the Serial Number as the parameter.
If the query returns a record, check the ServiceID.
If it is not null, then query view vService_Address using the ServiceID as the parameter.
Set the Address label to the concatenated address returned by the query
Nov 9 '11 #23

Seth Schrock
Expert 2.5K+
P: 2,951
So if the textbox is going to go away, then where is the address or the address unknown going to appear?
Nov 9 '11 #24

P: 37
I wanted to put the code in the Form Load Event.

I was going to create a label and make the default "Address Unknown".

I was thinking the code would be like:
Private Sub Form_Load()

Me.lblAddress.Caption = "Address Unknown"

If Not IsNull(SerialNbr) Then
**do query**
Else if
***check ServiceID***
Else If
If Not IsNull (ServiceID) Then
****do query**
End If


is this do-able or can we use the dlookup in the queries. I'm just totally confused.
Nov 9 '11 #25

P: 37
On the Cart Form, insert an Address label under the “Cart Update” title.
In the Form Load subroutine, set the default caption to “Address Unknown”.
Query the Cart table using the Serial Number as the parameter.
If the query returns a record, check the ServiceID.
If it is not null, then query view vService_Address using the ServiceID as the parameter.
Set the Address label to the concatenated address returned by the query
Nov 9 '11 #26

P: 37
ok...
this is what I've come across....using the textbox example.
When I enter the serial nbr the address field is blank. if i switch to design view and them back to form view the address is there. any ideas why it does it this way?
Nov 9 '11 #27

Seth Schrock
Expert 2.5K+
P: 2,951
I'll have to retest what I've got because it worked fine for me. Can you email me your updated version? And make it how you want it with where the address is going to be.
Nov 9 '11 #28

P: 37
ok I will do that...
Nov 9 '11 #29

Seth Schrock
Expert 2.5K+
P: 2,951
Here is the code that I put into the Form OnLoad event:

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(ServiceID) Then
  2.  
  3.     If IsNull(DLookup("StreetAddress", "qryCombineAddress", "ServiceID=" & ServiceID)) Then
  4.         Me.lblAddress.Caption = "Address Unknown"
  5.     Else
  6.         Me.lblAddress.Caption = DLookup("StreetAddress", "qryCombineAddress", "ServiceID=" & ServiceID)
  7.     End If
  8. End If
This tests to make sure that a record was returned when the form was opened and then does the test of whether to use address unknown or to put the address.
Nov 10 '11 #30

P: 37
B I N G O!!!! THAT WORKED!
I don't know how to thank you.. You are amazing, thank you so much for your patience and your time. Maybe one day I will be where you are (Access and VBA knowledge)!
Nov 10 '11 #31

Seth Schrock
Expert 2.5K+
P: 2,951
Great! Don't forget to choose best answer. It helps me and others who might have a similar problem.
Nov 10 '11 #32

Post your reply

Sign in to post your reply or Sign up for a free account.