none
Enter ID Number

    Question

  • tblServiceInfo has a ServiceID number which is an auto number, I am trying to get cbCharge1 to enter at afterupdate the the ServiceID number into text box tbServiceID1........Thanks for any help......Bob

    Private Sub cbDailyCharge1_AfterUpdate()
    Dim recRate As DAO.Recordset
        Set recRate = CurrentDb.OpenRecordset("SELECT Rate FROM tblServiceInfo " _
        & " WHERE ServiceID LIKE '" & cbDailyCharge1.Column(2) & "'")
       tbDailyChargeRate1.value = recRate.Fields("Rate")
        tbDailyChargeRate1_AfterUpdate
    End Sub

    Wednesday, January 25, 2012 9:23 PM

Answers

  • Does the bound column of the combo box have distinct values?  If you have the same value in the bound column in more than one row, then selecting a row will select the first row with the value in the bound column.  As you are referencing the ServiceID as Column(2), i.e. the third column, but you only show two columns I can only assume that this is a hidden third column.  So if the first column is the bound column, selecting Full Training will actually select the row with a rate of 48 USD, even if you select that with a rate of 50 USD.

    If this is the case you need to somehow amend the definition of the combo box control so that its bound column contains distinct values.  If the hidden third column, which I assume is ServiceID, contains distinct values then you can simply set the control's BoundColumn property to 3, and amend your code to:

    Set recRate = CurrentDb.OpenRecordset("SELECT Rate FROM tblServiceInfo " _
        & " WHERE ServiceID = " & cbDailyCharge1

    I'm assuming that ServiceID is a numeric data type.

    Sorry for the proposal/unproposal of Bill's response.  An errant mouse click!


    Ken Sheridan, Stafford, England
    • Edited by Ken Sheridan Thursday, January 26, 2012 12:10 PM Typo corrected
    • Marked as answer by TurnipOrange Thursday, January 26, 2012 10:44 PM
    Thursday, January 26, 2012 12:09 PM
  • I added ServiceID "Number" to both tables

    Does that work?  Looking back at your earlier post I'd have thought you need to change the SQL statements to:

    CurrentDb.Execute "INSERT INTO tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
            & " DailyChargeRate,DailyChargeAmount, HorseID)Values(" & lngInvoiceID & ",'" & Format(tbStartDate1.value, "dd/mm/yyyy") _
            & "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," & tbTotalDays1.value & ",'" & cbDailyCharge1.Column(0) _
            & "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value & "," & cbHorseName.value & ")"
        End If


    and:


    CurrentDb.Execute "INSERT INTO tblDaily_ItMdt(HoldingFlg,IntermediateID,dtDate,HorseID,StartDate,EndDate,TotalDays,DailyCharge, " _
            & " DailyChargeRate,DailyChargeAmount)Values(" & Nz(chkHoldingFlg1.value, False) _
            & "," & val(tbIntermediateID.value) & ",'" & Format(Now, "dd/mm/yyyy") & "'," & cbHorseName.value & ",'" & Format(tbStartDate1.value, "dd/mm/yyyy") _
            & "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," & tbTotalDays1.value & ",'" & cbDailyCharge1.Column(0) _
            & "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value & ")"


    Ken Sheridan, Stafford, England
    • Marked as answer by TurnipOrange Friday, January 27, 2012 6:13 PM
    Friday, January 27, 2012 12:00 PM

All replies

  • Bob

    I for one, don't understand what you are trying to accomplish. Can you explain a little more? There have been several views of your post but no responses.


    Bill Mosca
    http://www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals
    • Proposed as answer by Ken Sheridan Thursday, January 26, 2012 12:02 PM
    • Unproposed as answer by Ken Sheridan Thursday, January 26, 2012 12:05 PM
    Wednesday, January 25, 2012 10:49 PM
  • The problem is  cbDailyCharge1 can have the same data but a different rate in  tbDailyChargeRate1.value

    Like this

    Full Training                 $48

    Full Training                 $50

    So I need something that will show the difference in both records because some times when i open my form it will change the tbDailyChargeRate from 48 to 50 and i think that is because if the same text in cbdailyCharge1

    Thanks for the help...Bob

    Thursday, January 26, 2012 7:43 AM
  • Does the bound column of the combo box have distinct values?  If you have the same value in the bound column in more than one row, then selecting a row will select the first row with the value in the bound column.  As you are referencing the ServiceID as Column(2), i.e. the third column, but you only show two columns I can only assume that this is a hidden third column.  So if the first column is the bound column, selecting Full Training will actually select the row with a rate of 48 USD, even if you select that with a rate of 50 USD.

    If this is the case you need to somehow amend the definition of the combo box control so that its bound column contains distinct values.  If the hidden third column, which I assume is ServiceID, contains distinct values then you can simply set the control's BoundColumn property to 3, and amend your code to:

    Set recRate = CurrentDb.OpenRecordset("SELECT Rate FROM tblServiceInfo " _
        & " WHERE ServiceID = " & cbDailyCharge1

    I'm assuming that ServiceID is a numeric data type.

    Sorry for the proposal/unproposal of Bill's response.  An errant mouse click!


    Ken Sheridan, Stafford, England
    • Edited by Ken Sheridan Thursday, January 26, 2012 12:10 PM Typo corrected
    • Marked as answer by TurnipOrange Thursday, January 26, 2012 10:44 PM
    Thursday, January 26, 2012 12:09 PM
  • Thanks Ken ,

    My Combo box below (cbDailyCharge1)

    First Column  (Description) only Column showing

    Second Column (Rate) Not Showing

    Third Column (ServiceID) Not Showing

    I am getting a syntax error on this line below

    tbDailyChargeRate1.value = recRate.Fields("Rate")
        tbDailyChargeRate1_AfterUpdate

    My Text Box (tbDailyChargeRate1) Shows the Rate in a text box for calculation!

    Thanks Bob

     

    Thursday, January 26, 2012 9:17 PM
  • I think you had better put a breakpoint in at that assignment line and check in the Immediate Window by typing in

    ?recRate.Fields("Rate").Value

    (you should be able to just use recRate("Rate") instead - no need for .Fields)

    (or just by hovering over the recRate.Fields("Rate") code) to see what the value being returned is.

     

    Why would you even need the query if the combo has the rate in it's second column?


    Bob Larson, Access MVP 2008-2010, 2011
    Thursday, January 26, 2012 9:33 PM
  •  

    Thanks Bob, I am getting this with hover Cursor

    recRate.Fields("Rate")=< Object variable or With block variable not set>

    Thanks Bob

    Thursday, January 26, 2012 10:08 PM
  • As Bob implies in the last line of his latest post, why are you establishing a recordset object at all?  As Rate is the second column of the combo box's RowSource surely you can simply assign the value to tbDailyChargeRate1 if it is a bound control:

    Me.tbDailyChargeRate1 = Me.cbDailyCharge1.Column(1)

    or, if it's an unbound control set its RowSource property to:

    =[cbDailyCharge1].Column(1)

    You still haven't said what the combo box's BoundColumn property is.  I'd assume it should be 3.

    Ken Sheridan, Stafford, England
    Thursday, January 26, 2012 10:24 PM
  • Sorry Ken, Bound 1

    Thanks Bob

    Thursday, January 26, 2012 10:41 PM
  • Ken. I changed it to Bound Column 3 andnow your code is working :)

    Thanks Bob

    Thursday, January 26, 2012 10:44 PM
  • Thanks Guys ,Just on little problem now all my invoices in my database now are blank in the Combo Box, But all the Daily rates are there and add up correct, Beacuse its bound to Column 3 now (ServiceID) is there away around that?

    Thankas for all your help.....Bob

    Thursday, January 26, 2012 10:53 PM
  • You'll have to explain the underlying model in more detail.  As the combo box's value is now the ServiceID, the natural assumption would be that it should be bound to a foreign key ServiceID column in the form's underlying table or query.  But without knowing more about the role of the form and what underlies it, there's not a lot can be said.

    Ken Sheridan, Stafford, England
    Thursday, January 26, 2012 11:14 PM
  • Thanks Ken, The form has no record source, As you can see it is now updating the Service ID into Daily Charge there are 2 records I updated 7 and 40 are now entered

    I suppose I have to go to where the tables are being updated?

    Thanks Bob

    Thursday, January 26, 2012 11:27 PM
  • You'll have to assign the value of cbDailyCharge1.Column(0), i.e. the first column of the combo box to the DailyCharge column in the table.  Making the ServiceID the bound column is essential to enable you select a distinct row in the combo box, but you can still  reference the other columns.
    Ken Sheridan, Stafford, England
    Friday, January 27, 2012 12:16 AM
  • Thanks Ken, cbDailyCharge1 has a row source query below, How do I get Column(0) to update in DailyCharge?

    Thanks for your help...Bob

    SELECT tblServiceInfo.ServiceInfo, tblServiceInfo.Rate, tblServiceInfo.ServiceID
    FROM tblServiceInfo
    WHERE (((tblServiceInfo.ServiceInfo) Is Not Null) AND ((tblServiceInfo.ynMonthly)=False))
    ORDER BY tblServiceInfo.Rate DESC;

    Friday, January 27, 2012 12:28 AM
  • As your form is unbound, I assume you must have some code somewhere to update the row in the table, so you'd do it there.  It would be a case of changing the code so that instead of referencing the combo box (which of course is really a reference to its Value property as the default property) you'd reference cbDailyCharge1.Column(0).  If you can post a snippet of the code you are using we should be able to give you chapter and verse.

    I'm signing off for tonight now, so I'll respond further tomorrow if necessary.

    Ken Sheridan, Stafford, England
    Friday, January 27, 2012 12:52 AM
  • Thanks Ken this Form updates to to tables tblDailyCharge and tblDaily_ItMdt depending how you open it!..........Thanks for your help...Bob

    Private Sub subSetInvoiceDetailsValues()
            
     
            CurrentDb.Execute "INSERT INTO tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
            & " DailyChargeRate,DailyChargeAmount, HorseID)Values(" & lngInvoiceID & ",'" & Format(tbStartDate1.value, "dd/mm/yyyy") _
            & "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," & tbTotalDays1.value & ",'" & cbDailyCharge1.value _
            & "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value & "," & cbHorseName.value & ")"
        End If
    ===========================================================================================
    Private Sub subSetInvoiceItMdtDetailsValues()

    If IsNull(Form_subAdditionCharge.chkHoldingCharge.value) Then
        Form_subAdditionCharge.chkHoldingCharge.value = Nz(Form_subAdditionCharge.chkHoldingCharge.value, 0)
    End If
        CurrentDb.Execute "INSERT INTO tblAddition_ItMdt(HoldingFlg,IntermediateID,dtDate,HorseID,ChargeID,DayNo,Hold,HoldTimes,HoldAmount,TimesAmount,ChargeNumber,AdditionCharge, " _
            & " AdditionChargeAmount)SELECT " & IIf(Form_subAdditionCharge.chkHoldingCharge.value = -1, True, False) & "," & val(tbIntermediateID.value) & ",'" & Format(Now, "dd/mm/yyyy") & "'," & cbHorseName.value & "," & "ChargeID" _
            & ",DayNo,TimesAmount,Hold,HoldTimes,HoldAmount,AdditionCharge, " _
            & " AdditionChargeAmount FROM tmpAdditionCharge"
       
        If IsNull(tbTotalDays1.value) = True Or tbTotalDays1.value = "" Then
           
        Else
            If IsNull(chkHoldingFlg1.value) Then
            chkHoldingFlg1.value = Nz(chkHoldingFlg1.value, 0)
            End If
            CurrentDb.Execute "INSERT INTO tblDaily_ItMdt(HoldingFlg,IntermediateID,dtDate,HorseID,StartDate,EndDate,TotalDays,DailyCharge, " _
            & " DailyChargeRate,DailyChargeAmount)Values(" & Nz(chkHoldingFlg1.value, False) _
            & "," & val(tbIntermediateID.value) & ",'" & Format(Now, "dd/mm/yyyy") & "'," & cbHorseName.value & ",'" & Format(tbStartDate1.value, "dd/mm/yyyy") _
            & "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," & tbTotalDays1.value & ",'" & cbDailyCharge1.value _
            & "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value & ")"
        End If

    Friday, January 27, 2012 1:11 AM
  • Neither of these 2 tables have a field for ServiceID, tblDailyCharge and tblDaily_ItMdt ........Thanks Bob
    Friday, January 27, 2012 1:15 AM
  • I added ServiceID "Number" to both tables, Bob
    Friday, January 27, 2012 1:30 AM
  • I added ServiceID "Number" to both tables

    Does that work?  Looking back at your earlier post I'd have thought you need to change the SQL statements to:

    CurrentDb.Execute "INSERT INTO tblDailyCharge(InvoiceID,StartDate,EndDate,TotalDays,DailyCharge, " _
            & " DailyChargeRate,DailyChargeAmount, HorseID)Values(" & lngInvoiceID & ",'" & Format(tbStartDate1.value, "dd/mm/yyyy") _
            & "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," & tbTotalDays1.value & ",'" & cbDailyCharge1.Column(0) _
            & "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value & "," & cbHorseName.value & ")"
        End If


    and:


    CurrentDb.Execute "INSERT INTO tblDaily_ItMdt(HoldingFlg,IntermediateID,dtDate,HorseID,StartDate,EndDate,TotalDays,DailyCharge, " _
            & " DailyChargeRate,DailyChargeAmount)Values(" & Nz(chkHoldingFlg1.value, False) _
            & "," & val(tbIntermediateID.value) & ",'" & Format(Now, "dd/mm/yyyy") & "'," & cbHorseName.value & ",'" & Format(tbStartDate1.value, "dd/mm/yyyy") _
            & "','" & Format(tbEndDate1.value, "dd/mm/yyyy") & "'," & tbTotalDays1.value & ",'" & cbDailyCharge1.Column(0) _
            & "'," & tbDailyChargeRate1.value & "," & tbDailyChargeAmount1.value & ")"


    Ken Sheridan, Stafford, England
    • Marked as answer by TurnipOrange Friday, January 27, 2012 6:13 PM
    Friday, January 27, 2012 12:00 PM
  • Thanks Ken , That worked it is now putting the Daily Description into the 2 fields. The Only dilema I have old records are now showing blank data in all my combo boxes , the Rates are correct and the Invoices still add up . But it would be to much of a job to go through and select the combo boxes from 2000 invoices!The reports are showing the correct Daily Descriptions from my old Invoices. Maybe an update query might do it?

    Thanks for your help.....Bob

    Friday, January 27, 2012 6:13 PM
  • What is the Daily Description?  I don't think that's been mentioned in the thread before now.  The important question is whether the data is correct in the table for both the old rows and the new rows.  You say the old  data is apparently correctly represented in the reports, but is the new data?  If not, which I would not be surprised to be the case, then this points to a discrepancy between the nature of the data being inserted into the table now compared with that which was inserted before you made the changes.  If the old and new data were both correct then one would expect it both to be represented correctly in both the form and report.

    I don't see that we can do a lot to diagnose the problem on the basis of the very limited view we have of your application.  It would analogous to our identifying a fault in your car's engine control system merely by looking at the dashboard.  If the petrol gauge is at zero, then it's reasonable to assume the car won't start because it's out of fuel, but that doesn't mean that filling the tank will get you going if the fuel pump is also not working.  All we can do is suggest avenues to explore, which in this case means looking at the data, both old and new, and identifying what it is about the data which determines that the new is being represented correctly in the form and the old not, which I think is what you are saying.


    Ken Sheridan, Stafford, England
    Friday, January 27, 2012 10:20 PM
  • Thanks Ken , As you can see above I have only changed the Bound Column to 3 in The first Combo Box Only and as you can see it does not show the First line in my query in the combo Box , If I change the Bound Column to 1 it appears again, Do I need column 3 bound or can i change it to Column 1

    Column1 -Daily Description

    Column2 - Rate

    Column3 - ServiceID

    Thanks for your effort....Bob

    Friday, January 27, 2012 11:04 PM
  • That does reinforce what I said before that you need to examine the data in the underlying table.  The bound column has to be ServiceID, as that is the only column in the control's RowSource which has distinct values.  It follows from this that the combo box in the bound form which you've illustrated here should be bound to a column (DailyCharge?) which is a foreign key referencing the primary key of tblServiceInfo.

    This takes us back to your SQL statements.  By changing these to insert the value of the first column you are inserting the ServiceInfo value into the column, not the ServiceID value.  I think you need to change those SQL statement from & cbDailyCharge1.Column(0) _back to & cbDailyCharge1, and
    the bound combo box in the form you've illustrated should have a RowSource of:

    SELECT ServiceID, ServiceInfo
    FROM tblServiceInfo
    ORDER BY ServiceInfo;

    and set up as follows:

    BoundColumn:   1
    ColumnCount:   2
    ColumnWidths:  0cm;8cm

    If your units of measurement are imperial rather than metric Access will automatically convert the last one.  The important thing is that the first dimension is zero to hide the first column and that the second is at least as wide as the combo box.

    The RowSource for the unbound cbDailyCharge1 control in the unbound form should remain as you have it, with its BoundColumn property as 3 so that its value is the hidden ServiceID for the service you select from the visible column.

    But the above is predicated on the column to which the service combo box in the form you've illustrated being bound to a foreign key referencing ServiceID, the primary key of tblServiceInfo.  This is what I'd expect of your model, but you need to be sure that is the case.

    In essence you need to be sure that the unbound combo box, the SQL statements and the bound combo box are all singing from the same hymn sheet.


    Ken Sheridan, Stafford, England
    Saturday, January 28, 2012 12:27 AM
  • Thanks Ken, My query DailyRate needs the Rate,  as I have them in order of Price not ServiceInfo, Also the rate shows in the drop down list to choose which price you want to charge.

    SELECT tblServiceInfo.ServiceInfo, tblServiceInfo.Rate, tblServiceInfo.ServiceID
    FROM tblServiceInfo
    WHERE (((tblServiceInfo.ServiceInfo) Is Not Null))
    ORDER BY tblServiceInfo.Rate DESC;

    My AfterUpdate of my ComboBox is:

    Private Sub cbDailyCharge1_AfterUpdate()
    Dim recRate As DAO.Recordset
        Set recRate = CurrentDb.OpenRecordset("SELECT Rate FROM tblServiceInfo " _
        & " WHERE ServiceID LIKE '" & cbDailyCharge1.Column(2) & "'")
       tbDailyChargeRate1.value = recRate.Fields("Rate")
        tbDailyChargeRate1_AfterUpdate
    End Sub

    My Column Count 3

    Column Width 9.2cm;2cm;0cm

    Thanks for your help...Bob

     

    Saturday, January 28, 2012 2:22 AM
  • It's not a question of what columns are returned by the RowSource property, you can return however many you like so long as the bound column is ServiceID.  In the language of the relational model ServicID is a determinant of Rate, so once you select the row in the combo box's list with the correct service and rate you can then insert the value of ServiceID from that row into the relevant table.  You can then join that table to the tblServiceInfo table in a query used as the RecordSource of a bound form and show both the service (in a combo box which bound to ServiceID but showing the ServiceInfo text value from the tblServiceInfo table) and the rate in a Rate column in the query returned from the tblServiceInfo table by virtue of the relevant row from tblServiceInfo being referenced by the foreign key column in the referencing table.

    I see no reason to establish a recordset object, that's just overcomplicating the issue.  Just build and execute an INSERT INTO statement which includes the value of the cbDailyCharge1 control to be inserted into the foreign key column in the relevant referencing table.

    Ken Sheridan, Stafford, England
    Saturday, January 28, 2012 1:06 PM
  • Thanks Ken, I dont think I can bound the form as it is used by 6 Tables 3 Intermediate and 3 Final

    tbDaily_ItMdt, tblAddition_ItMdt, tblInvoice_ItMdt

    tblDaily, tblAddition, tblInvoice

    Daily is for Daily Charges Rates

    Addition is for Additional Charges

    Invoice is for Totals and Clients to be charged except for tblInvoice_ItMdt as the clients have not be distributed to the share in each horse!

    I have uploaded a strip down version of my database StableSize(Demo).accdb at

    www.box.com/shared/hi10eyb0fx

     

    If you dont mind having a quick look, If you click on a horse in the right hand pane the Invoice will appear........Thanks for your help with this...Bob

     

    Saturday, January 28, 2012 9:07 PM
  • Well, I had a look at your file, but it's far too complex for me to be able to spare any time on, and even if I could I don't think it would serve any purpose.  If it were possible to perceive a robust logical model underlying the database I might be able to comment further, but no obvious model is apparent to me at least, and in the absence of such I'm afraid I can't add anything to  what I've said already.
    Ken Sheridan, Stafford, England
    Sunday, January 29, 2012 12:33 AM
  • Ok Thanks Ken. I will make A query to idenify Duplicate data in the same field ServiceInfo , and put a warning on the form "No Duplicate Enties". The data base is in operation with a lot of Studs and Stables and seems to work very, very well for the lay men as most people are horse people doing there own accounts, They say it saves them a a staff member part time doing there book work, I make no money out of it, just a hobby/Passion ;)

    Regards Bob Vance

    Sunday, January 29, 2012 1:16 AM