Solved

Place values from a pop-up subform into another subform in a form???

Posted on 2003-11-27
9
734 Views
Last Modified: 2011-10-03
I have a pop-up subform, that pops up over another pop-up subform with a subform inserted. . . . whew thats a mouthful.

I'll try again - I have a pop-up MAIN form (that covers the entire desktop) with a datasheet subform(ITEMS) inserted.  On this MAIN form I have a command button that opens up another pop-up form (PRICECHECK) that is 6" by 6" and centered on top of the MAIN form.

On the PRICECHECK form I have a couple combo boxes, a couple text boxes and a command button - the user can select items from the combo boxes which in turn populate the text boxes with values.  I want to be able to click the command button and have the values in the 1 of the combo boxes and the text boxes into my ITEMS subform (inserted into my MAIN form).

I originally was going to insert the values into the table that the ITEMS subform is bound to, but the problem is that my ITEMS subform is linked to my MAIN form via a RECORDID (autonumber) field so I need to insert the values into the ITEMS subform so that the RECORDID value will be the same as the RECORDID displayed on my MAIN form.

Any ideas?

Holy moly I hope that makes sense - tougher to describe than I thought!

Max
0
Comment
Question by:MaxwellTurner
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 3

Accepted Solution

by:
DMurray earned 250 total points
ID: 9834292
I've done a similiar thing using the OPENARGS arguement for opening forms - except the other way round :P (sending data from main for to popup form).

What I think you need to do

1. You need to pass the current RECORDID from the main form to the PRICECHECK form. Then store that in a variable then do the insert in the click event of the button on the subform.

To pass a variable between forms : this is how you do it with OPENARGS

On the onclick event of your button to open PRICECHECK on the main form...

sub button onclick()
dim int_recordId as integer

int_RecordId = val(me.recordID.value)
DoCmd Openform "Pricecheck", acPopup,,,,, int_RecordId
end sub

Then on your PRICECHECK onOpen event

'global variable
dim Int_TheRecord as integer

Private Sub Form_Open(Cancel As Integer)

If Me.OpenArgs = Null Then
        int_TheRecord = 0
    Else
        int_TheRecord = Me.OpenArgs
End If
end sub

sub popupbutton onclick()
Dim db as database
Dim insertSQL as string
'Do your calculations with your text boxes etc on the form

'Make your INSERT sql string
insertSql = "INSERT INTO TABLE(recordId, value)values("' & int_theRecord &"',,,)" ..... etc"

db.execute(insertSQL)

'then refresh/requery your main form with the value you just inserted.
[forms]![Main].requery


db.close
set db = nothing
end sub

Not totally complete code - but should be enough to explain how to pass the RECORD id between the two forms and then insert this.

Hope that helps
DMurray


0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9834302
Hi MaxwellTurner,
> ====================================================================
>  Question from MaxwellTurner                              11/28/2003 12:18AM
>  Points: 250                                          Type: Unlocked
>  Title: Place values from a pop-up subform into another subform in a form???
> ====================================================================
> I have a pop-up subform, that pops up over another pop-up subform with a
> subform inserted. . . . whew thats a mouthful.
>
> I'll try again - I have a pop-up MAIN form (that covers the entire desktop)
> with a datasheet subform(ITEMS) inserted.  On this MAIN form I have a command
> button that opens up another pop-up form (PRICECHECK) that is 6" by 6" and
> centered on top of the MAIN form.
>
> On the PRICECHECK form I have a couple combo boxes, a couple text boxes and
> a command button - the user can select items from the combo boxes which in
> turn populate the text boxes with values.  I want to be able to click the
> command button and have the values in the 1 of the combo boxes and the text
> boxes into my ITEMS subform (inserted into my MAIN form).
>
> I originally was going to insert the values into the table that the ITEMS
> subform is bound to, but the problem is that my ITEMS subform is linked to my
> MAIN form via a RECORDID (autonumber) field so I need to insert the values
> into the ITEMS subform so that the RECORDID value will be the same as the
> RECORDID displayed on my MAIN form.
>
> Any ideas?
>
> Holy moly I hope that makes sense - tougher to describe than I thought!
>
> Max

well, hmmmm yeeeesss ;-)

(trying to interprete here):

You have a form A.

You open a form B.

When you close form B, you want form A to be updated and reflect some choices you made in form B.

Correct ?


Kind regards,
Sven
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 9834377
Sven:

Actually, in FORM B, the user chooses a product's item number from a combo box, and this populates the other text fields in FORM B based on the selection.  Then I want to click a command button and have the value of the combo box and the text boxes to be inserted into a subform that is inserted into FORM A(which is open & directly behind FORM B).

While waiting for an answer, I think I got it to work using:

  Forms![pricingsheetsAdd]![oemPSITEMS subformAdd].Form![OnDate].Value = Now()
  Forms![pricingsheetsAdd]![oemPSITEMS subformAdd].Form![Itemno].Value = Me!OemnoList
  Forms![pricingsheetsAdd]![oemPSITEMS subformAdd].Form![Description].Value = Me!ShowDescription
  Forms![pricingsheetsAdd]![oemPSITEMS subformAdd].Form![Price].Value = Me!OEMPrice

in the OnClick event of my command button.

To get the RECORDID in the subform, I forced the autonumber by inserting a space into a field in my MAIN form - this advances the autonumber to it's next increment in the MAIN form & subform.

DMurray:

Your method looks much more complex than what I ended up using.  I am not a great VB programmer, so the easier it is, the better.

I'd love to give these points away, and I have 2 other questions:

When I use the code(via command button) above to insert the values into my ITEMS subform from my PRICECHECK form, it inserts the values into the first row of my subform - perfect.  Now if I change the values in my PRICECHECK form and click the command button to insert the values into my ITEMS subform, they go into the same record - I want them to be inserted into the next record!!!

Also, do either of you guys know how I can keep a pop-up form in the foreground, even if I click onto another form that is in the background?

Max
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9834407
almost right, I think theres is a form a.5 in there which actually receives the values, subform to the main form.

So to recap, there is Form A, with a subform A.5 and a popup B. When values are selected on PopUpB from 2 cmbBoxes they are (through some confusing routing through a coupla textboxes) eventually stored on Subform A.5 after a button Click on PopupB.

hey this is fun, can anyone say this yet another way? :P

John
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:DMurray
ID: 9834410
To the first quesiton - it think its because you are editing the current record on the MAIN form and changing the values via your popup.

You should be INSERTING new data rather than changing the values - hence why you have the problem. I still assert that using openArgs is the best way to do it - and will help you ten fold down the line if you need to ever pass variables between forms.

To the second question....in the form design properties on the other tab - set it the form to "POPUP" and it should always be the active window until you close it

DMurray
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9834425
ok youze were posting whilst I was typing :)

Max, I just want to point out something regarding your first question which you feell you resolved... You appear to be using the popup to select values from a recordset to populate textboxes (still on the popup) for the sole reason of passing the values of the textboxes back to the mainform (or the mainforms subform). This is unecessarily convoluted. The slection from the combobox can simply pass the Record ID back to the mainform ( or even reside there )  and the mainform can use the Record ID to lookup any values it needs displayed. Im not sure you will get in trouble the way you went (although that space in a record thing worries me a bit) but something like that makes following the logic a bit dificult when you need to make changes.

If I've misread what you are doing, and why, then ignore this, or better yet, enlighten me :)

my 2 cents.

John
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 9834759
DMurray - hmmmm,  I think you are correct.  I will give your suggestion a go tommorrow morning.  I did not know how to pass variables between forms - it will be very handy in the future!

jobrienct - you described it exactly!  To explain why I am doing that:

My main form:  3 text boxes, RECORDID & SALESPERSON (both disabled - SALESPERSON's value is based on my LOGIN form(minimized) & RECORDID is an autonumber), and PREPAREDFOR.   It also has 2 datasheet subforms inserted.  Entering something into the PREPAREDFOR textbox will normally trigger the autonumber RECORDID, however, the user has the option of leaving that field blank.  From that field the user can enter data into either or both of the 2 subforms(both of which hold the same RECORDID as the main form if it has been 'triggered' on the main form).  If the user choses NOT to enter data into the PREPAREDFOR textbox I insert the " " into the PREPAREDFOR textbox to get a RECORDID in the main form and thus the 2 subforms as well.  In subform1 I insert the " "(only if isNull) OnEnter, and the subform that this question is about, I insert the " "(only if isNull) when I click the command button on the PRICECHECK form.  

I hope that clears it up abit.  Your comment made me think.  I could actually accomplish waht I want with a combobox in my main form and another in my subform - might save me a whole lot of trouble!

Will give it another try in the morning.

Max
0
 
LVL 10

Expert Comment

by:jobrienct
ID: 9835493
making you think is what it's all about around here. let us know how it goes, I really think you should move it to main if you can, and it sounds like you can :)

John
0
 
LVL 1

Author Comment

by:MaxwellTurner
ID: 9839544
OK I got everything working! woo hoo!

DMurray:  I did mostly of what you suggested.  I was able to get the RECORDID into my form using:

Me![RecordID].Value = [Forms]![pricingsheetsAdd].[RecordID]

where Me!RecordID is a text box on my small pop-up. I made the small popup modal so that the RECORDID (on the small form) stays the same as the main form's until I close it.  I INSERTED  the values as you suggested rather than CHANGING THE VALUES of the current record & when I requeried the subform, it all worked great - thanks.

jobrienct:  I understand what u mean by moving the poup form into my main form as a subform, but I chose to stick with the popup because I can make things more user firendly for the user this way, and my main form would have too much going on with an addditional subform - could be done, but I think this way is a bit cleaner - perhaps you have to actually see what I am doing to understand what I mean.

Max
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now