updating and underlying table field with a combo box selection

I want to use an ID value from a combo box on a subform to update the same ID 
value in the form's underlying table.

My main form is: frm_Street_Joiner_Main
My Subform is: frm_Street_Joiner_Sub

My Subform table is: tbl_Street_Joiner
My Mainform is: frm_Street_Joiner_Main

The combo box on my subform is called: StreetName, with a column count of 
two but the bound column is the actual street name.


SELECT QRY_Street_Names_Joiner_Master.Street_Names, 
QRY_Street_Names_Joiner_Master.StreetNameID FROM 
QRY_Street_Names_Joiner_Master ORDER BY 
QRY_Street_Names_Joiner_Master.Street_Names, 
QRY_Street_Names_Joiner_Master.StreetNameID;  

The combo box selects the street name, but I want the StreetNameID to update 
to the form's underyling table and also appear in a seperate box named 
StreetNameID.




0
Utf
12/2/2007 1:53:01 AM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
638 Views

Similar Articles

[PageSpeed] 0


"efandango" wrote:

> I want to use an ID value from a combo box on a subform to update the same ID 
> value in the form's underlying table.
> 
> My main form is: frm_Street_Joiner_Main
> My Subform is: frm_Street_Joiner_Sub
> 
> My Subform table is: tbl_Street_Joiner
> My Mainform is: frm_Street_Joiner_Main
> 
> The combo box on my subform is called: StreetName, with a column count of 
> two but the bound column is the actual street name.
> 
> 
> SELECT QRY_Street_Names_Joiner_Master.Street_Names, 
> QRY_Street_Names_Joiner_Master.StreetNameID FROM 
> QRY_Street_Names_Joiner_Master ORDER BY 
> QRY_Street_Names_Joiner_Master.Street_Names, 
> QRY_Street_Names_Joiner_Master.StreetNameID;  
> 
> The combo box selects the street name, but I want the StreetNameID to update 
> to the form's underyling table and also appear in a seperate box named 
> StreetNameID.
> 
> 
> 
> 

Try and analyse this routine and make it work for your application. 
Basically, you open the table by using an sql statement and update the table. 
Then you copy the data from the table up to your main form.

Public Function CalculateStockLevel(strmainform, strSubformcontrol)

Set db = DBEngine(0)(0)

'tabActivity
Dim strSQL1 As String
'tabPartsMovements
Dim strSQL2 As String
'tabParts
Dim strSQL3 As String

Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset

Dim x As Integer
Dim y As Integer
Dim n1 As String
Dim strTemp As String
strTemp = ""
n1 = Chr(10) & Chr(13)
Dim strSearch As String

Dim ActivityArray() As String
Dim max As Integer
Dim QuantitySumArray() As Double

'tabActivity
strSQL1 = "SELECT * from tabActivity"

'tabPartsMovements
strSQL2 = "select * from tabPartsMovements where tabPartsID = " & 
Forms!frmParts!tabPartsID & ";"

'tabParts
strSQL3 = "select * from tabParts where tabPartsID = " & 
Forms!frmParts!tabPartsID & ";"

Set rs1 = db.OpenRecordset(strSQL1)
Set rs2 = db.OpenRecordset(strSQL2)
Set rs3 = db.OpenRecordset(strSQL3)

If rs1.RecordCount = 0 Then
MsgBox ("no records in tabActivity")
GoTo err_handler
Else
rs1.MoveLast
'MsgBox (rs1.AbsolutePosition)
End If

rs1.MoveLast
max = rs1.RecordCount
rs1.MoveFirst
ReDim ActivityArray(max, 2)
ReDim QuantitySumArray(max)
strTemp = "tabActivity: " & n1 & n1

For x = 0 To rs1.RecordCount - 1
y = x + 1
ActivityArray(y, 1) = rs1!Text
strTemp = strTemp & "y :" & y & ", " & rs1!tabActivityID & ", " & rs1!Text & 
n1
rs1.MoveNext
Next x

''''''''''''MsgBox strTemp

strTemp = ""
If rs2.RecordCount = 0 Then
MsgBox ("no records in tabActivity")
GoTo err_handler
Else

'MsgBox (rs2.RecordCount)
End If

If rs2.RecordCount = 0 Then
    GoTo err_handler
Else
    rs2.MoveLast
    max = rs2.RecordCount
    ''''''''''''''''''''''''''MsgBox ("rs2 recordcount " & rs2.RecordCount & 
", Max: " & max)
    rs2.MoveFirst
    'strTemp = "tabPartsMovements: " & n1 & n1
    For x = 1 To max
                'strTemp = strTemp & "Abs.Pos.: " & rs2.AbsolutePosition & 
", " & "Act: " & rs2!Activity & ", " & "Qty: " & rs2!Quantity & n1
                
                QuantitySumArray(rs2!tabActivityID) = 
QuantitySumArray(rs2!tabActivityID) + rs2!Quantity
        rs2.MoveNext
    Next x
    
    '''''''''''''''''''''MsgBox (strTemp)
    
    strTemp = "max check" & n1
    For x = 1 To max
    strTemp = strTemp & Str(x) & n1
    Next x
End If


'''''''''''''''''MsgBox strTemp

'create sum on activity id (total on purchase, total on sales etc.)

'Quantity Summary
rs1.MoveLast
max = rs1.RecordCount
rs2.MoveFirst

strTemp = "Quantity Summary" & n1
For x = 1 To max
strTemp = strTemp & "x: " & x & Str(QuantitySumArray(x)) & n1
Next x
'''''''''''''''MsgBox (strTemp)

rs3.edit
rs3!Sales = QuantitySumArray(2)
rs3!Purchases = QuantitySumArray(1)
rs3!DepotIssuance = QuantitySumArray(6)
rs3!DepotReceived = QuantitySumArray(5)
rs3!ReconOut = QuantitySumArray(8)
rs3!ReconIn = QuantitySumArray(7)
rs3!DepotReceived = QuantitySumArray(5)
rs3!WshopOut = QuantitySumArray(4)
rs3!WshopIn = QuantitySumArray(3)
rs3!StockReconciliationOut = QuantitySumArray(15)
rs3!StockReconciliationIn = QuantitySumArray(14)
rs3!OnBoardIssuance = QuantitySumArray(10)
rs3!OnBoardReceived = QuantitySumArray(9)
rs3!CreditNotesPendingOut = QuantitySumArray(12)
rs3!OrdersPendingIn = QuantitySumArray(11)
rs3!StockOnHand = rs3!SubtotalII - rs3!SubtotalI
rs3!SubtotalI = rs3!Sales + rs3!DepotIssuance + rs3!ReconOut + rs3!WshopOut 
+ rs3!StockReconciliationOut
rs3!SubtotalII = rs3!Purchases + rs3!DepotReceived + rs3!ReconIn + 
rs3!WshopIn + rs3!StockReconciliationIn
rs3!StockOnHand = rs3!SubtotalII - rs3!SubtotalI
rs3!SubtotalIII = rs3!SubtotalI + rs3!StockOnHand
rs3!SubtotalIV = rs3!SubtotalII
rs3!SubtotalVI = rs3!SubtotalIV + rs3!StockOnHand + rs3!OnBoardReceived
rs3!SubtotalV = rs3!SubtotalVI
rs3!StockAvailableI = rs3!StockOnHand + rs3!OnBoardReceived - 
rs3!OnBoardIssuance
rs3!SubtotalVIII = rs3!SubtotalVI + rs3!StockAvailableI + rs3!OrdersPendingIn
rs3!SubtotalVII = rs3!SubtotalVIII
rs3!StockAvailableII = rs3!StockAvailableI + rs3!OrdersPendingIn - 
rs3!CreditNotesPendingOut


'stock taking
' With rstCustomers
'         ' Populate recordset.
'         .MoveLast
'         ' Find first record satisfying search string. Exit
'         ' loop if no such record exists.
'         .FindFirst strCountry
'         If .NoMatch Then
'            MsgBox "No records found with " & _
'               strCountry & "."
'            Exit Do
'
'               strCountry = "Country = '" & strCountry & "'"

'strSearch = "Activity = '" & Str(13) & "'"


strSearch = "tabActivityID = 13"
With rs2
..MoveFirst
..FindLast strSearch

If .NoMatch Then
    MsgBox ("no stock take date")
Else
rs3!LastStockTaking = rs2!Date
End If

End With

rs3.Update
Forms(strmainform)(strSubformcontrol).Requery

err_handler:
End Function

0
Utf
12/2/2007 9:10:00 AM
Reply:

Similar Artilces:

How do I delete a text box from an Excel spreadsheet?
Rightclick on it and select cut If the textbox is from the control toolbox toolbar, you may have to click on the designmode icon first. Uncle Binky wrote: -- Dave Peterson ...

updating prices manually
Hello, Just upgraded to Money 2006 from Money 2003. I am tracking several investments for which prices cannot be updated online. In 2003, it was possible to enter the daily price manually by right-clicking on the investment name and entering the price for a specific date. I can't find this function in 2006. Does anybody know where it is, or if it still exists? Thanks. I should add that after right-clicking on the investment name, in 2003 I used to click on "Update Price", after which I could enter the price and date. "paula" wrote: > Hello, > > J...

WMP 11 error C00D1163 (after update KB971513)
Windows Media Player 11 will not play DVDs. I always get error C00D1163 "Windows Media Player cannot play this DVD because there is a problem with digital copy protection between your DVD drive, decoder, and video card. Try installing an updated driver for your video card." DVDs I have owned for years are now impossible to play (so are rentals). I am having the same problem as others in this forum, although my system configuration is different -- which indicates this is a widespread problem. It is an interesting coincidence that my problem started immediately ...

query with inline dummy table
Is there a way in Access to create a query that contains the table records within the query itself? I want to avoid creating a dummy table and just use values within the query definition. I was thinking about using syntax similar to the insert into statement I would use to populate the dummy table, but I'm not sure if I have a syntax problem or I'm trying to solve an impossible problem. The query I'm thinking of might look something like this: select * from values("test"); -or- select * from ( ("1/1/07","2/1/07","3/1/07","4/1/07&qu...

workaround for non normalized table
I've inherited a database that relies heavily on a non normalized table. Until I can convince the general manager that I can normalize the data without losing any records, I've got to have a workaround for certain situations. Namely, I need to ensure that data is not being badly reproduced at various stages of our operations. I would therefore like to reference the information directly from the main table in order to populate certain information in related tables. In this case, the main table uses [Order Number] as its primary key, and ties it to a bunch of information like ...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Is there a wizard to export and match fields?
I would like to export products from my table into a csv that has different field names. Is there a wizard that would allow me to select which fields to export and what fields they match up to in the new file? Instead of exporting a table, create a query and alias the field names to the ones you want in the CSV, then export the query. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L "ZenMasta" <me...

Field service table structure and field definitions
The 9 SDK does not reference or define the fields or tables for the Field service component. Where can I find this information? I need to move RMA into a data warehouse and am having significant trouble identifying the data flows. Thanks, Will You may want to contact I.B.I.S. They wrote the application. -- Charles Allen, MVP "WS" wrote: > The 9 SDK does not reference or define the fields or tables for the Field > service component. Where can I find this information? I need to move RMA > into a data warehouse and am having significant trouble identifying th...

Comments field
I would like to add some notes for a Payee but the Comments field under the Payee is not big enough. Is this the only place I can makes these type of notes? I want to put in information such as lenght of contract, type of service they provide, costs, etc.... Thank you. No other solutions (in Money) are coming to mind. "Apr" <noreply@mail.com> wrote in message news:u8WcsBuFHHA.3304@TK2MSFTNGP05.phx.gbl... > Is this the only place I can makes these type of notes? Apr wrote: > I would like to add some notes for a Payee but the Comments field > under the Pa...

How to add summary fields to Group Footer in Access Reports?
How do you add a Summary fields to Group Footers in Access? I have a Detail field I want to Sum in the Group Footer in my report. -- Pat Dools ...

Error in code exportin tables to excel
I'm tryin to export a table to excel with the code: Private Sub Command4_Click() On Error GoTo Err_Command0_Click Dim stDocName As String stDocName = "F01_UT" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, F01_UT, "C:\TRY\Tbls", True Exit_Command0_Click: Exit Sub Err_Command0_Click: MsgBox Err.Description Resume Exit_Command0_Click End Sub However, Access displays the message: Compile error : Syntax error What I'm doing wrong? If I want to export more tablas do I have to write more lines (DoCmd.TransferSpreadsheet acExport, acSpr...

Updateing data when closing forms
I am using MS Access 2000 I have a table that has several required fields. I developed a form to add records to the table. In addition to the table fields, the form has a form-close button constructed with the button wizard. When I enter a record that does not have the required information and close the form with the “Close Window” (X) button on the tool bar, the appropriate error message appears telling me that there is missing data. (Just what I want) When I use the close button on the form, the form closes with no message and without adding the record. How do I give the button on the f...

VBA code to hide all the tables on form open
I don't want people to use a blank mdb to import my tables. I manually hide them all. However, after running the macro to delete all records and import from .txt, the table become unhide. I do the importation on daily basis. I posted to macro newsgroup and asked way to hide table after importation action macro but got no answer. Maybe it cannot be done in macro? If so, I need VBA code to hide all the tables on form open. Thanks. Hiding your tables won't prevent people from being able to import them into a blank mdb. All they have to do is ensure that they've set the datab...

Free shipping for selected customers
Our Store Ops system is setup to always charge shipping on all orders for all customers (the "Automatic shipping" option in Store Ops configuration is checked). For 99% of our customers, this is correct. However, there are a select group of employees who work in our warehouse. They do not need the garments shipped to them, so they should not automatically be charged shipping. There are also some high-end customers who we normally provide free shipping as a courtesy. Our system is also setup to received orders from a website (using a custom web interface). It can feed the cu...

Combo box choice outcome
I have a combo box with the following choices: Checks_Walkthrough Checks_Monitor Research_Case Inventory_Maint Task_Cable Task_Misc New Issue What I want is if anything BUT "New issue" is selected, two fields in the same form as the combo box are populated by other data in the table the above list is pulled from. I can get this to happen by itself. Me.Text27.Value = Me.imac.Column(2) Me.model.Value = Me.imac.Column(3) If "New Issue" is selected, I want another form 'New_case' to open and enter the required data there. I can get this to work alone ...

Passing Values from One Form to Another Including a Combo Box
Hi, hope someone can help with passing two values from one form to another by way of a command button. I have spent a week on various code taken from this site, but still no luck. Please ... someone help!! The form I am passing values from is called PATIENT HISTORY-Form. On this form, I need to pass a date from a field called DateSFESigned and I also need to pass information collected from a Combo box, Combo91. The command button is called Command119. The form that the values are being passed to is called Personal Habits- Form. Thank you in advance for any help on this matter. Maurita ...

Status field in POP10500
Can anyone tell me what "0" in the Status field of POP10500 means? I have shipments that show up on my Purchase Order Processing Document Inquiry window with no corresponding invoice. However, when I attempt to match them to a purchasing invoice, I get a message saying, “This line item has been fully received and invoiced, You may continue, or you may delete this line item and enter a different one.” Using Query Analyzer, I see records with a POTYPE of 2 (Invoiced Receipt) matching up to the shipments. However, the "Status" for these two records is "0". Th...

OUTLOOK FIELD NAME
Hi at all!!!I have this problem....I want export my mail from Lotus Notes into Microsoft Outlook 2000. I have the script already available but I don't know the field name of Outlook document. Something can help me??? Bye.... how in the heck did you get that far, i can't figure any of it out ----- giuliano wrote: ----- Hi at all!!!I have this problem....I want export my mail from Lotus Notes into Microsoft Outlook 2000. I have the script already available but I don't know the field name of Outlook document. Something can help me???...

dynamically filtered pivot table
I'm trying to make a pivot table that will dynamically hide a section o its contents based on a boolean operator the user can set. Becaus this boolean is used in several places, I don't want to require th user to manually set the visibility parameters for the pivot table. It seems like the only way to do this effectively is to have th booleans set by a button, and have the button not only toggle th boolean, but also change the visibility in the pivot table. here's the code i've tried: Sub ToggleButton1_Click() If ToggleButton1.Caption = "Include" Then 'C...

Exporting a table from Access 2003 to Excel 2007
I am trying to export a table from Access 2003 with more than 100,000 records into Excel 2007 and am not having any luck. I do not find the 2007 file extension in the drop down and choosing the latest version only exports part of the table. Any thoughts on whether this is possible to do? -- Carol Hi Carol, This is even hard to do with Excel 2007. You won't be able to do it directly with Excel 2003. You could export the file as a comma separated value with a csv suffix. By default Excel usually opens csv files. However you may lose formatting and other stuff. I ju...

update prices online
I'm using Money 2003 with Windows 98. Everything was fine until I downloaded Money updates in April 2004. I lost my capability to update quote prices online (the "update prices online" option in portfolio was gone) and only gives me the "update prices manually". Does anyone know what caused this? How can I get the "update prices online" option back? Thanks for any support you could give. If you have Money 2003 standard and installed it in April 2003, then it is possible that the update prices online feature has expired. -- Glyn Simpson, Microsoft...

Text in pivot table limited to 255 characters
I am using a pivot table as an efficient way to aggregate text responses from a large data set. However, the pivot table cuts off the text after the first 255 characters (similar to when you copy a worksheet by using the move/copy option). How can I overcome this? I have tried putting the pivot table on the same sheet as the dataset, but that does not work. I should also note that these pivot tables are then fed into an automated report through a complicated set of VLOOKUPs, etc. The pivot table aggregates several questions and responses from many areas of the datset into one discr...

Moving fields on extender windows
Hi! Can someone tell me how I can move the fields on the extender windows? -- Marisol Mortera Because of the way Extender stores data, you cannot move fields like you would using Modifier. You need to go to the Extender windows design and set up another field with the information. Then you will need to move data behind the scenes from the existing field to the new field. Alternatively, you could export the data; delete the data in the window; and import it. "Marisol" wrote: > Hi! > > Can someone tell me how I can move the fields on the extender windows? > -- &...

How to keep the application even when show desktop is selected
Hi, I am developing an application which is a sort of reminder of your daily tasks. I want this application to remain on the top right corner of desktop even when "show desktop" is selected. However it gets minimised.........Any helps appreciated. Perhaps you want to consider "Active Channel Technology" and making your display part of the "Active Desktop"? In article <D580FA88-9C89-4D3F-AB9A-8374A9DE266B@microsoft.com>, Katiyar@discussions.microsoft.com says... > Hi, > I am developing an application which is a sort of reminder of your dail...