Help With Loop With Text Box In Subform.

Oct 27, 2006

hi guys i was wondering if you can help me please, in my main form i have a text box which the user enter an item number , and a combo box with the main contracts., then a continuous subform which has a combo box with the subcontracts that belong to that main contracts, in the subform i also have a txtPriceA, and txtPriceB. my problem is that with the code i have the priceA and priceB get added in different rows and all the other fields get added correctly but with the columns priceA and priceB empty.


this is what i tried

Private Sub Command94_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant

Dim strSQL As String


Set rst = CurrentDb.OpenRecordset("tblMain")

For varItem = 0 To Me!frmAddSubform.Form!cmbSubContracts.ListCount - 1 'combo box from subform
rst.AddNew 'add new row for each subContract in combo box

rst!SubContract_ID = Me!frmAddSubform.Form!cmbSubContracts.Column(0, varItem)

rst!MainContractID= me.txtMainContract.value
rst!ItemNumber = Me.txtItem.Value

rst!PriceA = Me!frmAddSubform.Form!txtPriceA.Value (this is supposed to be added in the columm PriceA in the same row as the 1st row added from rst!subContract_ID. )

rst!Priceb = Me!frmAddSubform.Form!txtPriceB.Value (this is supposed to be added in the columm PriceB in the same row as the 2nd row added from rst!subContract_ID. )


rst.Update
Next varItem


rst.Close



But I get this:

ID (autonumber) Item# Main Contract Subcontract PriceA PriceB
1 123 12345 12345-1
2 123 12345 12345-2

3 $15.00 $ 15.01
4 $15.50 $ 16.01

I should only have two rows added instead of four....like this

ID (autonumber) Item# Main Contract Subcontract PriceA PriceB
1 123 12345 12345-1 $15.00 $ 15.01
2 123 12345 12345-2 $15.50 $ 16.01


:confused:

also, i have my reasons why i have repeating rows.

View Replies


ADVERTISEMENT

Modules & VBA :: Loop In Text Box

Feb 23, 2014

I have a form where a textbox has shows the NameNo from the NameNo column in a db. At the moment I am using a dlookup to set the textbox = to the NameNo, but obviously this is only returning the 1st value in the column. How to get this form to automatically display each NameNo, creating a pdf of the form and then moving on tto the next nameno. I have the code to make the pdf working so far, all I need now is to figure out how to use the textbox to loop through all the NameNo's in the table.

View 3 Replies View Related

Modules & VBA :: Loop Through SubForm RecordSet?

Jun 4, 2014

Is there a way of looping through a non update able continuous subform, and using information from each line.

Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?

View 3 Replies View Related

General :: Loop - From One Continuous Subform To Another

May 21, 2014

I have attached an image of what i am trying to achieve so here goes:

I need to add data from one subform to another - problem is they are both continuous forms and both subforms.

The part in green is where it needs to go, and the white is where the data is held. The links have already been created, i just need to get the info from subform 1 to subform 2.

I have attached code but it only moves line selected / first line.

Dim dst As DAO.Recordset
Set dst = Me.frmAS9102_Material_LINK.Form.RecordsetClone
With dst
.MoveFirst
Do While Not .EOF

[Code] .....

View 4 Replies View Related

Modules & VBA :: Loop Through All Text Boxes On Form?

Aug 23, 2013

I have about 25 text boxes that get populated based on a financial transaction; quite often about half of them are empty. i wanted to gray them out if they are emtpy (.enabled = false). I could put a series of IF statements in the vba for each one, but its bulky and time-consuming. I've done for each record in tables and recordsets. Is there a way to do a for each textbox on a form?

View 3 Replies View Related

Forms :: Loop Through Records And Copy One Of Fields For Each Row Into Another Subform

Apr 12, 2013

I have a subform for which I want to loop through all the records and then copy one of the fields for each row into another sub form.

I came up with the bellow code but i get an error when I run it.

The error is an "error 438".

Code is at follows and I am copying the field called price:

Do Until Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].EOF
[Roll Out - Sign items added].Form![Price] = [Roll Out - Sign items pick list].Form![Price]
Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].MoveNext
Loop

View 4 Replies View Related

Modules & VBA :: Writing To Text Boxes On A Form Variable / Loop

Nov 16, 2014

I some code that is reading the first 32 lines of a text file and paste the results into 1 text box on individual lines. I would like each line to go into an individual text box.

I have 32 text boxes named txt_1, txt_2, txt_3
I have a variable named ThisLine holding the line of the text file.
I have a variable named textBoxName holding the name of the control.

Need to putting these together to write the line to a text box .

Code:
Sub ReadFile()
Me.txt_newData = ""
Me.txt_OrigData = ""
'again, we need this strange thing to exist so that ...
Dim fso As New FileSystemObject

[Code] .....

View 1 Replies View Related

Forms :: Display Row Text From Subform Column In Unbound Text Box Of Main Form

Jan 6, 2014

My database has Main form and a Sub form. On main form i place one unbound text box named investigations. In subform of which datasheet there is a column named TestCode. I want unbound text box (Investigations) to display row values which selected in a column (TestCode) of subform.for example:Investigation field should display "CBC,HB,ALP".

View 5 Replies View Related

General :: Text Box To Displaying Some Text When Subform Value Is Null

Sep 5, 2013

I have a form that is displaying a subform (of which source is a query). I have got the data from one of the columns in the subform going into a text box (entered in the control source) however when the field is empty it comes with a #Error in the textbox.I want the textbox is display some text is the subform value is null.

View 11 Replies View Related

How To Search Subform With Text Box

Mar 5, 2013

how to search subform with textbox and button in form

View 2 Replies View Related

Unbound Text Box To Subform

Mar 26, 2014

I have an unbound text box, that is determined from a users Environ details, and i would like to link this to a subform the subform is made from a cross tab query and basically i only want the subform to show the users details which is in the text box.

View 1 Replies View Related

Text Size For Colunm's In Subform

Apr 4, 2005

well, I have managed to get everything to work. Its a great feeling. the only thing I am having trouble with now is cosmetic.

http://www.lanschoolyard.com/newwindow.jpg
http://www.lanschoolyard.com/bar.bmp

Notice how the size of the text is a lot bigger than the size of the same text in the first image?

How do I reduce the size of the text? I have looked in all the boxes and tried everything but it just doesnt want to change the final output text size.

View 1 Replies View Related

SubForm Hiding Text Field

Jun 15, 2005

Ok this is a bit long winded and I will try to explain,

I have a main form, with a title at the top, then a row of buttons underneath and underneath the buttons I have a sub form.

Some of the buttons can have a dual function (or multiple function) so rather than open up another form I have hidden text below the button, so that when the mouse moves over the button, the text becomes visible. this gives the impression of a drop down menu off that button.

However I cannot get the text to appear over the top of the subform, it only appears behind it.

To give an example of what the buttons and drop downs are for, one of the buttons is a reports page, rather than open another form I can have a list of the differnt reports I wish to run (currently they are text boxes with an on click event).

any advice woudl be much appreciate and even an alternative to what I am doing if anybody has one.

Many thanks

MattP

View 5 Replies View Related

Transfer Text Box Data To Subform

Aug 23, 2005

This is a price check form so it doesnt need to be saved it just needs to display the part no info and quantities with price
If i enter a part no into a text box(PN1) on the main form it enters the data into a textbox(PN2) on a subform.
Im using a continuous form on the subform and the master/child link is the Part No text box.
is there a way to lock the first item to the first line and then be able to enter a second item to display on the next line.
I have made the size of the continuous form to one line in my subform but i am only able to enter one part no into the subform because when i setfocus to the PN1 textbox on the main form it clears the first line.

thanks

View 1 Replies View Related

Subform Shows A Number, Not Text...

Dec 8, 2006

It's my understanding of general database practice that an Autonumber field should be used as a primary key in normalisation tables. I realise my understanding could be wrong, and I'm quite happy to change that practice if need be.

I have my normalisation tables. For the most part these consist of an Autonumber field ([UID]) as the primary key, then an indexed, no duplicates text field with the normalisation data. For this case, we'll say it's a list of departments ([DepartmentID]).

I have a form for entering users. Again, the PK is an autonumber field. I used a wizard to create a combo box. I selected the fact that I want to store the value in [DepartmentID], NOT [UID].

This works fine, I can add my users and the combo boxes work, although I had to change the bound column to 2, as 1 is selected by default although it is [UID].

I then created a subform based on a query of all users. This subform is not bound to the main form in any way, it simply shows the result of the query.

When entering users, upon saving a record, the form is updated and the new user shown in the subform. Great, apart from "txtDepartment" in the subform showing [UID], and not [DepartmentID] as stipulated.

I have a horrible feeling this is to do with [UID] being my PK, but I'd like someone to tell me I'm wrong. I can quite happily remove the autonumber field [UID] and make the unique [DepartmentID] field the PK, but this just doesn't seem "correct" to me.

Should I do this? Should I have an autonumber field as the PK?

I realise this is more a design theory question than a form problem as such, but anything that clarifies my understanding of correct design practice will help.

EDIT:- I'd also like to say that I did it this way to get away from using the "LOOKUP" field type at table design time, specifically to avoid this very problem of numbers showing up when I want the text there!

View 3 Replies View Related

Autofill 3 Text Categories In Subform

Nov 11, 2004

I have a form for recording site details and have been trying to autofill three of the four categories.

What I have is a Site Index Table that contains the site ID, Name, Description and Type. I want the results to
be recorded in an Affect Table that will also contain text data associated with these fields that I will enter
in manually.

I then have a form with the four fields; the site ID and Type are both bound to the Affect Table that records all the data
associated with each site. The Name and Descritpion source their information from a seperate query of the Site Index Table.

So what I want to do is to select the site ID on the form from a combo box I've created, and once selected it will automatically fill in the relevant text data into the Name, description and type categories on the form. I've been trying scripts with the DLookup function but have continued to encounter errors. If anyone can help PLEASE help me as i'm a novice when it comes to Access

Thanks

View 3 Replies View Related

Forms :: Default Value For Subform Text Box

Jul 25, 2013

I have a mainform and a datahseet subform.

Mainform = FrmProj_ProjectEntry
Subform = FrmProj_ProjectEntry_Sub1

On my mainform I have a text box (FrmProj_ProjectEntry.txtDueDt) where I enter an expected date.
On my subform I also have a textbox (FrmProj_ProjectEntry_Sub1.txtDueDt) for entering an expected date.

I would like the entered date on the mainform to become the default expected date on the subform for new records only. Once records are created the expected date can then be changed for each record.

How would I achieve this on an after update event on FrmProj_ProjectEntry.txtDueDt?

View 4 Replies View Related

Clear All Text Boxes On A Subform

Jan 15, 2015

I am using this code:

Code:
Dim ctlTextBox As Control
For Each ctlTextBox In Me.Controls
If TypeName(ctlTextBox) = "TextBox" Then
ctlTextBox.Value = ""
End If
Next ctlTextBox

to clear all text boxes on a Main Form.When I try the same code in the Form Load of the sub form - it says I can't assign a value to this object.I just want the Main Form and Sub Form textboxes to be empty when they both Load up.There is a selection combo box that will populate the main form and the sub form [Master-Detail] once a selection is made.Right now the main form has all empty textboxes but the subform datasheet is showing detail irrelevant records belonging to another master record and I want no data till a cmb box selection is made.

View 6 Replies View Related

Write Conflict In Subform Text Boxes

Apr 6, 2006

If, on the startup form of the attached mdb, you type anything in any of the text boxes of the FPersonale subform(which, in form view, is labeled "Shift schedule") you get a message titled "Write Conflict" saying that during the current session the record had been modified by another user, asking to choose between saving the record, copying the edits to the clipboard or discarding the edits.
I've tried to get rid of the message by putting Me.Requery at the end of the text boxes' AfterUpdate event code but the message still pops up.

View 2 Replies View Related

#Name Error In Form Text Box--passing From Subform

Apr 20, 2007

Hi--I am working on a database which is a modification of the following MS Access 2003 Order Management template:
(URL address blocked: See forum rules)=CT101426031033

In my database, I have a subform called sbfOrderDetails, which in its Form Footer has a text box that sums the items in the order and is called OrderDetailsTotal.

I am trying to pass that value to a text box in the main form called OrderSubtotal.

I have three questions:

1) What is the proper syntax to use to get the value of the subform control into the OrderSubtotal control on the main form? I have tried various permutations and continue to get a #Name? error.

2) If you look at the expression in the Order Subtotal control of the Add and Manage Orders form in the original Order Management template, it looks like this:

=[Order Details Subform].Form!OrderDetailsTotal

When I try to replicate that in my database, I cannot--if I add brackets around my subform name and press Enter, Access adds brackets around everything in the expression, i.e. =[sbfOrderDetails].[Form]![OrderDetailsTotal]
(which returns a #Name? error). If I edit out the brackets I don't want and then press Enter, it puts the brackets back in. In other words, Access keeps trying to "fix" my expression by either having brackets around each item, or no brackets at all! This is really annoying; is there any way to turn it off??

3) When sbfOrderDetails is set to Single Form view, I can see the Form Footer (and the value in the OrderDetailsTotal text box as well). When it is set to Datasheet view, I cannot. Why?

Thank you very much,
lcnlit

PS>I cannot attach my database since I am a new poster, and the forum is disabling the URL of the Microsoft template as well.

View 3 Replies View Related

Forms :: Summing In Subform Text Box With Criteria

Nov 14, 2013

I am trying to sum the values in a textbox on a subform using a textbox in that subforms footer. Currently I have the textbox's control source to be this:

=Sum(IIf([chkInvoiceSent]=True,[txtPaymentAmount],0)).

This isn't filtering the appropriate records and is instead summing everything.I want to sum payment amount only if the chkinvoicesent box is checked off. Is the proper way to do this with a Dsum? I wanted to do it with the sum because it is faster and more elegant, or so I thought!

View 3 Replies View Related

Forms :: Referring To Text In A Table On Subform

Mar 3, 2015

I have a form with a subform that has a table on it. The subform table is a price list with the fields Description, Details (which is hidden as it's too big for the table) and Price.

I want to be able to double-click on a chosen item from the table to display a pop-up showing the same details but with the Details field shown. I have the pop-up in place but I am struggling with displaying the chosen information.

How do I reference the fields on the chosen line in the table?

View 6 Replies View Related

Reports :: Hide Text Box If A Subform Is Empty

Aug 30, 2013

I have a report with some subreport in it. I have there on top a textbox with the title of the subreport. What I need is that if there is no data in the subreport, it should be invisible and only show up when there is data in the subreport.

View 3 Replies View Related

Forms :: Subform Visibility Based On Text Box Value

Jan 2, 2015

I am trying to get a subform to be hidden when the value of a textbox (txtAllocationID) is empty but when a ID number appears I would like it to become visible again.This is what I have tried so far but doesn't seem to work:

Code:
Private Sub txtAllocationID_AfterUpdate()
If Me.txtAllocationID.Value >= 0 Then
Me.frmStockAllocated.Visible = True
Else
Me.frmStockAllocated.Visible = False
End If

End Sub

My allocation ID is a primary key so all auto number generated and only an integer.

Scenario: When I enter a stock ID of 121 and search I want the subform to remain hidden as there is no Allocation ID related to that Stock ID (see attachment tblAllocated). If I was to enter a stock ID of 122 I want the subform to become visible as the stock ID has a relation with the Allocation ID 6 and therefore the textbox txtAllocation ID would display '6' in it.

View 2 Replies View Related

Forms :: Filter Subform Based On Text Box

May 23, 2013

I have researched but couldn't find any working filter criteria for subform criteria.i have used the code, searching the web but still no luck.I have attached the picture and sample northwind database (accdb). I just want to filter customers in Orders navigation using text criteria.

View 9 Replies View Related

Modules & VBA :: Populate A Text Box On Subform Of A Form

Mar 21, 2014

I am trying to populate a txtBox on the SubForm of a Form, Using a Set Value Function = DlookUp function. The DlookUp Function searches for a value in the table and compares it to a value of another txtBox of the subform .The problem is:

-Using the Left Switch on Mouse works fine, txtBox is populated correctly
-Using the Enter Key does not work; it clears the box used for the seaech data and does not populate the txtBox.

I tried placing the procedure in the After Update event and also placing it in the on Enter event to no avail.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved