UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using Data From Subform On Main Form In Datasheet View, Access 2016    
 
   
dsbeck
post Jun 5 2020, 03:42 PM
Post#1



Posts: 23
Joined: 17-April 14



I have a form/subform which I've attached a picture of for clarity. I have the data shown in the subform in datasheet view, so I can see all the lines on 1 screen.

I want to highlight a line (i.e. record 2) for the subform, and have certain data from this form populate into the main form. (Customer code = Customer code; Product Description = Product Description). I can then change this data in the main form, before saving (i.e. change sales price for the new quote, but keep the product description, product code, etc. the same as previous quote)

I can't figure out how to select the appropriate fields in the subform in VBA to "put" them into the main form ( Forms![Main Form]![customer code] = Forms![Sub Form]![Customer code] )

I hope this is clear enough.

Any help would be appreciated.

Thanks,

Dave
This post has been edited by dsbeck: Jun 5 2020, 03:43 PM
Attached File(s)
Attached File  UA.png ( 22.4K )Number of downloads: 7
 
Go to the top of the page
 
theDBguy
post Jun 5 2020, 04:05 PM
Post#2


UA Moderator
Posts: 78,445
Joined: 19-June 07
From: SunnySandyEggo


Hi Dave. When exactly did you want the copy over to happen? I don't see any button to do it. If you use a Form event, then you might overwrite existing values. Besides, storing duplicate information (if that's what you're trying to do) in multiple tables is not really a good design approach. Maybe you can explain more specifically what you're trying to achieve.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dsbeck
post Jun 5 2020, 04:12 PM
Post#3



Posts: 23
Joined: 17-April 14



I am not storing date in 2 places. The subform is based on a query that pulls data from a quote table (Current quotes)

This form is to create a new quote, and instead of retyping all the information, I want to be able to highlight the row that has the correct information (i.e. customer code, sales description, etc.) Populate this into the main entry form (fields on the left) - and then change the items on this form that need changing ( i.e. Price).

Clicking on the Save quote saves the data from this main form (data on the left) to the main quote database.

I was hoping to do something like "on focus" to populate the records in the main form?? I could also add a button to the left say "Use this data as a starting point", but it would be easier if I highlight the row, and it populates the fields in the main form....

Does this explain it further ... ( I understand it perfectly ... Explaining it is the challenge )
Go to the top of the page
 
theDBguy
post Jun 5 2020, 04:37 PM
Post#4


UA Moderator
Posts: 78,445
Joined: 19-June 07
From: SunnySandyEggo


Hi. Okay, if not in two different places, then it sounds like you're then storing redundant or duplicate information in your table. To properly normalize a structure like that, you'll need to create a Parent table to store the "common data" and simply use a Foreign Key field in the Child table. Also, as I tried to say in my previous post, using something like a Focus event would mean existing data will be overwritten. For instance, if you move the focus to record #1, the code will copy record #1 data in the new quote. However, as soon as you move the focus to record #2, then the same new quote will not contain a copy of record #2 and the data copied from record #1 is gone. This will continue to happen as you move from one record to another. If you say, let's create a "new" record before copying the data from the record with the focus, then as you move from one record to another, you could end up with a bunch of new records that you don't need or didn't really mean to create. I think it's better when there's an "intentional" action to take before creating the new record, such as clicking on a button.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
dsbeck
post Jun 5 2020, 04:52 PM
Post#5



Posts: 23
Joined: 17-April 14



I agree on the redundant information, but reality is that there is very little data that is redundant, and then you would require 2 different tables/data entry forms:

1) A Form to create things like Customer; Customer Code; Sales Description; etc. This in 1 table
2) A form to enter the price for the above items - and a seperate table for the above.

We're not talking a ton of extra data, and it makes it much faster to just enter the same things like customer; code; description; etc. in 1 table - but that's not the point of my question ....

As for an intentional action: I'm fine with making a button that says: Use this record as a starting point. The question is: How do I reference these fields into the main form. I click the button, and I want to put the [customer code] from the selected record in the subform into the main form field [customer code]

I can change any of the fields in the main form over and over again - it doesn't save the record until I hit "save quote" - so overwriting data is not really a concern either.


Dave
Go to the top of the page
 
orange999
post Jun 5 2020, 05:52 PM
Post#6



Posts: 2,110
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Dave,
Can you post a copy of your table designs?

Sounds like you might need something along this

tblCustomer----has 1 or many --> tblQuotes

--------------------
Good luck with your project!
Go to the top of the page
 
dsbeck
post Jun 5 2020, 08:40 PM
Post#7



Posts: 23
Joined: 17-April 14



How has this become about my table design. I still have yet to have someone tell me how to assign the value from the subform to the main form ???

This is the error message I get:

Run-time error 2450; Microsoft Access cannot find the reference form 'Quote entry Subform'

My code is simple:

Forms![quote entry]![customer code] = Forms![Quote entry SubForm]![customer code]

Why can't it find the subform "Quote entry Subform" when it is opened with the "Quote Entry" form is open??

I don't want to argue about my table designs - I get your point, but this is an existing database I'm just trying to fix, and redesigning all the table designs is not in the time allotment.

THanks,

Dave
Go to the top of the page
 
June7
post Jun 5 2020, 08:49 PM
Post#8



Posts: 1,512
Joined: 25-January 16
From: The Great Land


Exactly where is the code located - in what event? Controls on main form are UNBOUND?

A subform or subreport is created by installing a subform/subreport container control on form or report. That control has a SourceObject property which can be a table, query, form or report. I always name container different from the object it holds. Referencing a field or control on subform requires referencing the container name.


This post has been edited by June7: Jun 5 2020, 08:50 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
RJD
post Jun 5 2020, 08:49 PM
Post#9


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but your code is looking for a main form rather than a subform on the right side of the equality. I may be missing something, but you might try ...

Forms![quote entry]![customer code] = Forms![quote entry]![Quote entry SubForm].Form![customer code]

This assumes that [Quote entry SubForm] is the subform of [quote entry]. The .Form tells us that in the suggested code. See if this works - or we can look closer at this.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
dsbeck
post Jun 5 2020, 09:09 PM
Post#10



Posts: 23
Joined: 17-April 14



For now, I put it on the SubForm Field "Customer Code" on Double click event. I will change it, but I had to put it somewhere to try it.


Joe: I tried your code, got same error message. Here is screenshot from VBA.

My main form is [Quote Entry] My subform is [Quote entry subForm]. The subform gets data from a query tied to the subform.

Very simple stuff, just can't get the field reference figured out ...

Dave
Attached File(s)
Attached File  UA.png ( 15.48K )Number of downloads: 7
 
Go to the top of the page
 
RJD
post Jun 5 2020, 09:30 PM
Post#11


UtterAccess VIP
Posts: 10,566
Joined: 25-October 10
From: Gulf South USA


Hmmm ... could you post a db with relevant objects, compacted and zipped? Obviously there is something we are missing here. Perhaps we might spot the problem with the objects available live. Got to be a solution there somewhere. More eyes might see it.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
FrankRuperto
post Jun 5 2020, 09:32 PM
Post#12



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


Hi dsbeck,

I suggest you copy code and paste it in between the code tags, as in the example below, instead of posting it as an attached image.
The code tags can be generated by clicking on the right most icon. Likewise, you can use the other icons for things like inserting a weblink, quoting another UA member, etc.
Placing your mouse pointer on each icon briefly explains what they're for.

CODE
CODE GOES HERE

Attached File(s)
Attached File  CodeTags.png ( 75.97K )Number of downloads: 3
 

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
June7
post Jun 5 2020, 09:55 PM
Post#13



Posts: 1,512
Joined: 25-January 16
From: The Great Land


This simple code behind subform works for me.

Me.Parent.[Customer code] = Me.[customer code]

as well as

Forms![quote entry].[customer code] = Me.[customer code]

Strongly advise not to use spaces in naming convention.

This post has been edited by June7: Jun 5 2020, 09:57 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
dsbeck
post Jun 6 2020, 06:40 AM
Post#14



Posts: 23
Joined: 17-April 14



June7:

That worked perfectly !!


Thanks,


Dave
Go to the top of the page
 
tina t
post Jun 6 2020, 09:48 AM
Post#15



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


hi DBguy, i'm wondering about the following, as

QUOTE
Okay, if not in two different places, then it sounds like you're then storing redundant or duplicate information in your table.

it looks to me like the data being copied/pasted is foreign key data - tying each quote record to a specific customer and a specific product. copying the price is just a convenience, as a price is specific to each quote, and may or may not change from one quote to the next. i'm having trouble picturing how that would be considered as duplicate data. help, pls?

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Jun 6 2020, 10:25 AM
Post#16


UA Moderator
Posts: 78,445
Joined: 19-June 07
From: SunnySandyEggo


Hi Tina. I was referring to, for example, the field "Produce Description." Do you think that needs to be copied for every new quote?

QUOTE (dsbeck)
I want to highlight a line (i.e. record 2) for the subform, and have certain data from this form populate into the main form. (Customer code = Customer code; Product Description = Product Description).

In fact, depending on what "Customer Code" means, I would also question it, since "Customer" is probably a Foreign Key already.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Jun 6 2020, 12:41 PM
Post#17



Posts: 6,671
Joined: 11-November 10
From: SoCal, USA


QUOTE
I was referring to, for example, the field "Produce Description." Do you think that needs to be copied for every new quote?

no, i agree with you there, DBguy. in looking at the form in the screen snip, i was assuming that Product Description would be the display for an underlying foreign key back to a tblProducts. a closer look at the snip does not bear this out - not a combobox control - my bad! :( and i agree with you that customer code is more iffy; it would require a look at the business model to really tell if it's a static value for each customer, or a variable that needs to be captured point-in-time.

okay, i'm still on the same page with you (always good to know). i thought i was missing something logic-wise, but it turns out it was just bad eyes. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 09:54 AM