Search results

Did you ever find a solution, Greg? I am having a similar problem that I am at the early stage of trying to narrow down.

That worked great!!! Thank you very, very much vbaInet!!! For anyone else who might try this here is the final updated code that I am using: Private Sub Form_BeforeInsert(Cancel As Integer) Dim stemp As String stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0) If Val(stemp) = 0 Then...

Yes, I was beginning to think that too because when I manually entered new values into the table they would be placed at the beginning of the column (ex: ACS-10000, ACS-10001, ACS-1500, ACS-1501 ... ACS-9999). However, I'm really not sure if I understand how to carry out your solution. Could you...

This gives me a new value of "ACS-1500". It should be "ACS-10000" as the last number was "ACS-9999".

This gives me a "type mismatch" error for the line: If stemp = 0 Then Private Sub Form_BeforeInsert(Cancel As Integer) Dim stemp As String stemp = Nz(DMax("strAcsFileNo", "tblClaim"), 0) If stemp = 0 Then stemp = "ACS-1500" Else stemp = "ACS-" & Val(stemp) + 1 End If...

If I use "string" as the character type I will not be able to add "1" to it which is needed in order to advance to the next sequential index. In my table the character type is "text" and the number of digits allowed is 25 (actually far more than I need to allow).

The variant character type is needed to include the "ACS-" portion.

I was given the following code to enable me to prefix my primary key with "ACS-" and to start the numbering at "1500" but now I'm discovering that I cannot advance beyond the record number "9999" (ie: "ACS-9999"). Any ideas why?Private Sub Form_BeforeInsert(Cancel As Integer) Dim stemp As...

Thank you very much for your ideas and the quick replies, my sincere apologies for my delayed reply! I was thinking along the same lines regarding the use of the docmd.transferspreadsheet method.I like the way Access handles duplicates when paste appending tables. How can I include this in my...

I haven't written much vba code but I need to create an event that will select data from various defined ranges from an Excel spreadsheet and paste append that data into an Access table. Can anyone help me with this?

Though I haven't implemented it yet, I am anxious to try using a form. I'm sure I will come up with many other uses for this as well. Thanks again!

Thank you very much guys! I had no idea I could do something like that! What a nice tool!!!

I am using a parameter query to obtain data within a date range using the "Between" operator. There may be an occasion where a user may not want to filter by date at all. Is there a way to override this by using: Like "*" & [] & "*" ...or by using a wildcard?

Thanks for the heads up dcb! I am pasting the Excel data using "Paste-Append" from the table's File menu.

Solution: While viewing a table in the data sheet view, move all the fields anticpated to be blank to the right and paste append only the fields with data. Then the blank fields are not written to and they are populated by their default values.

Is there a way to fill blank fields with default values while appending data to a table?

I've noticed lately that when I copy a parameter query using a right click of my mouse that it initiates the parameter prompt. I am not able to do anything unless I enter in values for the parameters. Compact and Repair is no help. Should I be concerned? Is there something I can do to stop...

A freight brokerage software company combines incoming EDI files sent by various carriers and then converts them to .csv files to be forwarded to us.

Using a lookup table is a good idea. I had been thinking along those lines as I have used those a lot in Excel. I am not familiar with how to do it with Access but I figured there must be a way. I will have to look into that further. Thank you very much for your help!

EDI imports are new to my client and a new table structure is being created to accommodate it. My client currently uses a relational database to store Shipper & consignee data, so although EDI reduces the burden of data entry the new table structure being designed to meet the needs of the...