Subform always shows first record

nemPyong

Registered User.
Local time
Today, 03:54
Joined
Jul 5, 2013
Messages
18
So, let's say I have a table named FLOWER_SHIPPING that has field:
Date [Date]
Flower_name [Short Text]
Amount [Long Integer]
Sender [Short Text]
Address [Long Text]

Then I created a form named MAINF with FLOWER_SHIPPING as its RecordSource. The form only has one TextBox: Date.

Then in MAINF, I created a subform named SUBF with FLOWER_SHIPPING as its RecordSource, in Datasheet View. It shows all fields in FLoWER_SHIPPING as it is.

The link between MAINF and SUBF is Date.

I want SUBF to only show record based on the Date chosen in Date TextBox in MAINF. So far, yes it did what I want. But with problems:
1) [SOLVED] SUBF does show record based on the Date I've chosen in MAINF's TextBox Date, but it ALWAYS ALSO shows the first record on the FLOWER_SHIPPING table. And the date in the first record always changed into the Date I just input in the TextBox Date in the MAINF. Automatically.
Example: I chose 6/22/2013, and there are 2 records with that date. the SUBF will show 3 records: those 2 records + the first row of FLOWER_SHIPPING with its Date automatically set to 6/22/2013.
2) When I chose a date in MAINF, I have to click everywhere in the SUBF so that it will refresh its content. Can I have it refresh automatically everytime after a date chosen?

Some things:
1) I've tried searching the answer by googling, but since I'm a total noob in Access, I don't really understand the solution given...
2) I'm trying to minimize the using of VB code, so what I'm really searching of is the way of doing it without codes. But if there's no way to do it without codes, please let me know.
 
Last edited:
That's because you are changing the Date value of the record showing in the main form to whatever date you choose, thus creating however many records you expect plus 1 more (the one you just changed).

You need to have the main form not be bound to anything.
 
Also, before you get too deep into this development, you really need to split up this 'Address' field! I have to assume, since you need to have more than 255 characters, the limit for a short text field, that you're storing the entire address into one field, and this is going to jump up and bite you where you don't want to be bitten, sooner or later. Long Text Fields, what used to be called Memo Fields, should never be used to store data that will ever be needed manipulated in any way. You really need to have separate fields for street address, city, state and zipcode/postal code. The chances are very good that in any database connected with the delivery of anything, sooner or later you're going to want to sort or group your data geographical areas, and planning for this now is far, far, far easier than parsing the data components out later.

Linq ;0)>
 
Problem 1 solved. Problem 2 anyone? :o
I tried requery and it gave the same reaction.
Another way is clicking the date twice (in the Calendar for date type input), but I think it's not something user'll do intuitively... so...
How to refresh the form?

That's because you are changing the Date value of the record showing in the main form to whatever date you choose, thus creating however many records you expect plus 1 more (the one you just changed).

You need to have the main form not be bound to anything.

Thank you so much
I did as you said, unbound it, and then used (and customizing it, sure) the code I found on another thread:
Combo box After Update event:

Code:
If Combo11.ListIndex > -1 and Combo11.value & "" <> "" Then
     Me![Description Key].Form.Filter = "[DescriptionKey] = '" & Combo11.value & "'"
     Me![Description Key].Form.FilterOn = True
End If
It worked :)

Also, before you get too deep into this development, you really need to split up this 'Address' field! I have to assume, since you need to have more than 255 characters, the limit for a short text field, that you're storing the entire address into one field, and this is going to jump up and bite you where you don't want to be bitten, sooner or later. Long Text Fields, what used to be called Memo Fields, should never be used to store data that will ever be needed manipulated in any way. You really need to have separate fields for street address, city, state and zipcode/postal code. The chances are very good that in any database connected with the delivery of anything, sooner or later you're going to want to sort or group your data geographical areas, and planning for this now is far, far, far easier than parsing the data components out later.

Linq ;0)>

Actually, it's just an example I made randomly. But thank you very much for a very good information you gave me there, since I do use Long Text as type for some of my fields and gave no further thoughts about how it'll be used, just "It seems it'll be long, so let's just give it Long Text" :banghead:
 
The parent form and the child form have the same record sources ?
This sound very unusual for me. Why all this stuff when the easiest approach seems to be to have all necessary fields in one form then to filter this form by date ? Is there something that I misunderstand ?
 
The parent form and the child form have the same record sources ?
This sound very unusual for me. Why all this stuff when the easiest approach seems to be to have all necessary fields in one form then to filter this form by date ? Is there something that I misunderstand ?

Yep yep, it's not elegant :banghead:
So I need to show the table it in Datasheet View and it's editable, and don't know how to do it but using subform... Wh... Why didn't I think of searching "How to show table datasheet view into form" :banghead:
 
Ok, if you say that is more elegant...
Any way, try another approach:
Design a query that replicate your table. Then base the subform on this query.
 
Ok, if you say that is more elegant...
Any way, try another approach:
Design a query that replicate your table. Then base the subform on this query.

Yeah, cause I read somewhere when searching for a solution, it's not a good thing to make the source of your mainform and subform from the same table :(

I tried your approach, and it produced the same behavior as problems no.1. Even when I tried to reverse it, the query for the mainform, still it had the same problem :confused:
 

Users who are viewing this thread

Back
Top Bottom