473,322 Members | 1,699 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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.

31 1749
Seth Schrock
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
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
I don't see where it allow me to attach anything
Nov 4 '11 #6
nevermind...I found it
Nov 4 '11 #7
Seth Schrock
2,965 Expert 2GB
You will have to compress the file into a zip folder to attach it FYI.
Nov 4 '11 #8
can't get the db small enough to attach.
Nov 4 '11 #9
is it possible that I can email it to you?
Nov 4 '11 #10
Seth Schrock
2,965 Expert 2GB
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
2,965 Expert 2GB
That would work. {email address removed}
Nov 4 '11 #12
here is the db with the forms and query.
Attached Files
File Type: zip Database.zip (23.9 KB, 74 views)
Nov 4 '11 #13
Seth Schrock
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
to query the cart table is that a select statement like this?

Select * from tbl_Cart Where SerialNbr = ?
Nov 8 '11 #19
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
2,965 Expert 2GB
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
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
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
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
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
2,965 Expert 2GB
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
ok I will do that...
Nov 9 '11 #29
Seth Schrock
2,965 Expert 2GB
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
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
2,965 Expert 2GB
Great! Don't forget to choose best answer. It helps me and others who might have a similar problem.
Nov 10 '11 #32

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

Similar topics

3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
1
by: rcmail14872 | last post by:
I used the upsize wizard to change my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was...
2
by: rg | last post by:
Hi all, I have query about passing a parameter from form to a query. My situation is as follows: I have a query that pulls up some data from a table, however there is a condition that must...
2
by: cwhite | last post by:
I'm having problems with a form based query The user makes a selection from a drop box, there are only two choices: Current Former the user makes a choice and clicks the preview report...
5
by: SeanCly10 | last post by:
Hi all. I don't want to sound like a complete idiot here, but I'm somewhat limited in my coding knowledge, and I need some advice and help. I'm working on a database that will eventually be used...
1
by: jl2886 | last post by:
Private Sub AM_Best_Rating_Click() Me.AM_Best_Rating = DLookup("", "ICNameRating", "=" & Me.Insurance_Company) End Sub Me.AM_Best_Rating the value I want to be automatically input....
2
by: Ulv | last post by:
I don't get it !!! Stupid, stupid, stupid I have 2 tables. TblPostings: Id, autonumber, key BlockNo, text NoofItems TblItem:
1
by: dvorasnell | last post by:
Hello, I have a fairly simple database. In this dbase, there is a subform which basically holds menu values for the records. These values serve as the "notes" for each record. OK, I find that I...
5
by: iChappy | last post by:
I have a query based off of two fields in an Access form: Fiscal Week (user manual inputs #) & Fiscal Year (combo box defaulted to current year however may select other years) The query is a...
1
by: MikeMikerson | last post by:
Hello, I am need to create a subform (no problem) of a form based query (a problem). I need a form that will prompt the user to enter in a name, and the form will then display a query of the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.