Posts by ChrisPadgham

There are plenty of off-the-shelf inventory control products that can do this better and cheaper than writing something yourself. If your requirements are simple there is bound to be some freeware available.

If all three tables have the same primary key then all of the fields should be in the one table. There is no point in having them in separate tables.

A text field will allow alpha numeric input. You can put a mask on it to specify the format of the input but personally I wouldn't, I don't find them particualarly user friendly.

Doesn't sound like you should be storing the second date in a field, put a text box on the screen and in its control source put the expression

=dateadd("m",1, me.firstdate)

I think you will find that SQL uses the american date format mm/dd/yyyy, I usually find it safer to use dd-mmm-yyyy eg 14-jun-2010

If you have linked the form and subform using the Link Child and Master field properties (see attachment) then you should not be setting the Id in the child form, it will be populated automatically for you from the Master.

For the second problem you are better to set up valid drug names in a table, then change the input text box to a combo box that retrieves its list from that table. Set the LimitToList property to True. That way they will not be able to input invalid drug names.

Perhaps you could driving it off the parent form. If you are displaying the child form as a result of clicking a button, include in there the code. You will need to alter the image assignment statement

Me.mysubform.Image1.Picture = ImageLogoPath

SELECT TOP 10 StudentName, StudentMark FROM StudentResults ORDER BY StudentMark DESC;

another approach Retrieve your combo box values from a table with 2 columns as follows

cm,A
inches,A
mm,A
lbs,B
kg,B
g,B

etc

the source query for combo 2 should contain both columns with a condition on the second column of the value of the first combo, eg Forms![myform].combo1

in the click event of the first combo requery the second combo
private sub combo1_click
me.combo2.requery
end sub

I am not a C# programmer but something like below adjusted if necessary for C#

WHERE Shift.SDate >=#" + format$(tbStartDate.Text,"mm/dd/yy") + "# and Shift.SDate <=#" + format$(tbEndDate.Text,"mm/dd/yy") + "#");

You are better to put a OrderCancelledDate field in the table. It provides more information to the user. If is is null then the order has not been cancelled.

just install .NET 2.0!

you are creating a support nightmare by trying to cobble together some references.

perhaps you need to check the data types of each of the elements in the table.

is "id" a numeric field?

Here is a piece of code that does something similar to what you want I thnk.

Dim WhereClause As String
Dim AndStr As String

AndStr = ""
If Me.SearchName <> "" Then
    WhereClause = "(FirstName like '*" & Me.SearchName & "*' OR Surname like '*" & Me.SearchName & "*')"
    AndStr = " AND "
End If
If Me.SearchSuburb <> "" Then
    WhereClause = WhereClause & AndStr & "Suburb like '*" & Me.SearchSuburb & "*'"
    AndStr = " AND "
End If
If Me.SearchCompany <> "" Then
    WhereClause = WhereClause & AndStr & "CompanyName like '*" & Me.SearchCompany & "*'"
    AndStr = " AND "
End If

If Me.searchRep <> "" Then
    WhereClause = WhereClause & AndStr & "Rep='" & Me.searchRep & "'"
        AndStr = " AND "
End If

If Me.SearchStatus <> "" Then
    WhereClause = WhereClause & AndStr & "Status='" & Me.SearchStatus & "'"
    AndStr = " AND "
End If
PerformSearch:
Me.Filter = WhereClause
Me.FilterOn = True

I have this problem from time to time, and at the moment. I set a breakpoint in the code to debug. I remove the breakpoint after I am finished but the program still pauses at that point. Press F5 and it continues fine. Close the database and reopen it and it breaks at that point again.

I need to work out how to get rid of the breakpoint completely.

units sold is the quantity which is why you are getting the same answer. are you sure the problem is not best selling products by price and quantity.

<> is not equal to

Unless I am going senile there are no circumstances where three lengths cannot make a triangle.

Why don't you write an update SQL to update the table directly rather than bring it into a ds.

eg

UPDATE DurexBOL SET WebSvc=Yes WHERE WebSvc=No

below is the code from the vb help file for executing non-query SQL

Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

I think you should have ActiveSheet in the assignment line (12)

ExcelSheetFO.ActiveSheet.Cells(2, i) = "Some value"

You have bigger problems than the syntax errors. I suggest you revisit the data model. Also your joins between the Employee and job_title tables need defining.

Have you considered using either an array (choice 1) or a collection (choice 2) that you can iterate through

'choice 1
        Dim i As Integer
        Dim tbarray(10) As TextBox
        For i = 0 To Val(Form1.ChannelTextBox1.Text) - 1
            ' set your values
        Next i

        'choice 2
        Dim tblist As New Collection
        Dim tb As New TextBox
        tb.Parent = Me
        tblist.Add(tb)

unless you are using a fixed width font such as Courier New then you are better to go with a multiple column approach. Like the one below using a list view

Dim rowvals(1) As String

        Me.ListView1.Columns.Add("Given", 50)
        Me.ListView1.Columns.Add("Surname", 70)
        ListView1.View = View.Details
        rowvals(0) = "John"
        rowvals(1) = "Smith"

        Dim newrow As New ListViewItem(rowvals)

        Me.ListView1.Items.Add(newrow)
        rowvals(0) = "Joe"
        rowvals(1) = "Bloggs"

        Dim row2 As New ListViewItem(rowvals)

        Me.ListView1.Items.Add(row2)

I don't think you should use GETDATE, you should hold the date of purchase in ShopCart otherwise when you run this query later you will get a different result.

You can put joins within a select statement. I don't see your problem.

I don't quite get the problem are you saying that you are missing rows. if this is the problem and you are joining two tables you should check you are using an outer join.

You will probably need to export the table data, drop the table and rebuild and reload.

try using the Count property of the collection to test if there are any items in the collection.

if objOffice.count > 0 then
' rest of your code from while to end while
end if

also for the future wrap you code in code /code to make it easier to read

whilst you define param I can't see where you assign it a value or provide it to disp

Use the dateadd function to find the duedate based on daterented and length of hire. if length of hire is in days then

duedate = dateadd("d",LengthOfHire,DateRented)

to validate just use this
dim hold as string

hold=strings.right(text1.text,1)

if isnumeric(hold)=false then

msgbox"its a character"
else
msgbox"its a number"

end if

this will not check for a-z, they could enter a !, @, # etc and it would still say it is true. and you are assuming the string entered is exactly 8 characters long.

I think line 3 could also be:
If achar [!A-Z] then

Probably correct Ken, just call me old fashioned. :-)

I had a remarkably similar situation writing an app for a charity supporting single mothers to work out their childrens birthdays.

The difficulty is that it will give you a date in their birth year but not their actual birthday, so if you store it in that field you need some way of indicating that it is an approximation and not the actual date. If you choose a fixed date in the year, say 1st Jan, then at least your users will know that this has been estimated and not the actual birthday.

Me.DOB = "1/1/" & datediff("YYYY",me.age*-1,me.dateofEntry)

I try your first code, but it said "Index was out of range of Arry"
And it never works.
May you revise it a little bit please?
I really need to understand this assignment.
Thank you

sorry about that should arrays start at 0 not 1

words = Split(TextBox3.Text, " ")
        Label4.Text = UCase$(words(0).Substring(0, 1)) & UCase$(words(1).Substring(0, 1))

So what is wrong, is it not inserting the row.

I presume clsData is a class you have defined. you need to supply the code to that.

Personnally I wouldn't. Whilst Access supports this I have not found it robust enough. Also there are limitations on the size of access databases and they are easy to blow if you start storing images in the database. Can I suggest you store the image in the file system and store the path to the image in the database.

I'd say you need to do some normalisation on your data model. You should not be using two tables. Personally I would simply have a sold date in the stock table. When the sold date is null then the item is still in stock.

iif(isnull([Table Children]![Date of Birth]),[Table Children].[Age At Entry],DateDiff("m",now(),[Table Children]![Date of Birth]))

Not sure if this helps but that error message is usually generated when there are mismatch quotes in an evaluated string.

if you posted it in a format that I could read you would stand a much better chance of an answer

Dim words() As String
        words = Split(TextBox3.Text, " ")
        Label4.Text = UCase$(words(1).Substring(1, 1)) & UCase$(words(2).Substring(1, 1))

You may like to enhance it to cope with more than two words by enclosing it in a loop

for i = 0 to ubound(words)


next i

You are always retrieving and displaying the values for the first row because you are using a constant (o) in the row references

Rows(0)

you need to replace this with a variable that holds the ordinal position of the row you wish to retrieve.

You should not be storing the total in the database it is a derived value and should be calculated on the fly as you need it.

Storing derived values increases the complexity of your application and provides for data inconsistencies to arise. For example, if they don't balance how do you know which is correct.

I am thinking your approach is not completely robust. Without checking I imagine the 123.45 will return true to isnumeric which I imagine you don't want.

Personally, I am not a fan of masked text boxes because they are not that user friendly but it is a quick and easy way to achieve what you want. Use a mask of "0000000A" and set the ASCII only property to true.

If you want to test the 8th character to see if it is a letter here is the code

Dim aChar As String
        aChar = UCase$(Mid$(TextBox3.Text, 8, 1))
        If aChar >= "A" And aChar <= "Z" Then
            Label4.Text = "The 8th char is a letter"
        Else
            Label4.Text = "The 8th char is not a letter"
        End If

If you only want the last record, you may like to add TOP(1) to that however if you are working in a multi-user environment then you may not get the record you wrote.

It may be better to have a "OrderPrinted" column to record whether an order has been printed or not.

I presume these are coming from user input, in which case it may be simpler to restrict your inputs to valid combinations than try and interpret them later.

For example if we stick to 4 inputs for simplicity. If 3 & 4 can only be supplied if 1 is supplied then write some client side script to run on exit from 1. if 1 is Null then disable 3 and 4, otherwise enable 3 & 4. This way you only need to cater for valid combinations in your code.

with 9 boolean variables that is 512 combinations

If you are not into maths this is going to freak you out a bit but the best approach is to resort to binary. An integer in binary form consists of a series of 1s and 0s that can represent your 9 variables

eg 010101011 = 171

the left most 0 represents your first listbox
second digit (1) represents listbox 2
third digit (0) represents listbox 3
and so on.

this is achieved in .NET with a BitArray. In the example below I have set values 1 & 3 arbitarily. In your code you will set all nine elements of the bit array based on your list boxes.

the function BinToInt converts the bit array to an integer between 0 and 511 which represents to 512 different combinations.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim LineIn As String
        LineIn = ""
        Dim Int1 As Int32
        Dim test As New BitArray(9, False)
        
        
        test.Item(1) = True
        test.Item(3) = True
        Int1 = BinToInt(test, 9)
        Console.WriteLine(Int1.ToString)
        

    End Sub
    Public Function BinToInt(ByVal BAin As BitArray, ByVal BAdigits As Int16) As Int16
        Dim i As Int16
        Dim outint As Int16
        outint = 0
        For i = 0 To BAdigits - 1
            If BAin(i) Then
                outint = outint + 2 ^ i
            End If
        Next i
        BinToInt = outint
            
    End Function

Create a second table with columns for the variable data and an extra field/column effectivedatetime, note in this table PumpId is NOT the primarykey since there will be multiple records for each pumpid.

In the Before_Update & After_Insret events of the form place a piece of SQL to write the record into this table eg

currentdb.execute "INSERT INTO PumpHistory (PumpId, EffectiveDateTime, PumpStatus) VALUES (" & me.pumpid & ", Now, '" & me.pumpstatus & "')

I presume you know how to read text from a file, if not search for "read text from a file" in help

the code below uses a variation of select you may not have tried

Select Case True

which allows you to put a boolean expression in each Case statement.

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim LineIn As String
        LineIn = ""

        'open and loop through file one line at a time reading line into variable LineIn
        'below is the code to extract the data you are interested in and display it in a 
        'series of labels on the form you have set up for the purpose
        Select Case True
            Case InStr("Convergence Iterations", LineIn) <> 0
                Label1.Text = LineIn
            Case InStr("Number of Stations", LineIn) <> 0
                Label2.Text = LineIn
                ' etc ...
        End Select
        

    End Sub

Here is a simpler solution, note you add one because array elements start at 0 not 1. ie if there are 4 words they will be in array elements 0,1,2,3

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim teststr() As String
        Dim NoWords As Int16
        
        teststr = Trim(TextBox3.Text).Split
        NoWords = UBound(teststr) + 1
        Label4.Text = "Your sentence contains " & NoWords.ToString & " words"
        
        
    End Sub