Thank you Phil.
I didn't know about the AddNew method, so I learned something new today. I had to remove the i = i + 1 line because it wasn't necessary in a FOR... NEXT loop, it was skipping numbers. (Its output was 1, 3, 5, instead of 1, 2, 3, 4, 5.) Otherwise the code worked as intended.
Doug
dstorms
Last Activity: Jul 24 '17, 04:04 PM
Joined: Oct 30 '07
Location:
-
How do I run an append query with sequential numbers?
I've creating a form (frmPurchases) in which I add new records to a table (tblItems) starting with a number I can set (TagNo). If I buy 5 copies of "Harry Potter and the Prisoner of Azkaban" from Amazon and then apply stickers numbering from 41 to 45, I would enter the following:
TagNo: 41
Qty: 5
Vendor: Amazon
ModelName: HP-Azkaban
At the click of the button I want this output:
Amazon... -
I got the first half right, but the OnLoad event may not be working the way I'd hoped. I've tried these two expressions:
=[Me].[frmDigitalEquip ment]![EmployeeID]
=[EmployeeID]=[Me].[frmDigitalEquip ment]![EmployeeID]
but neither would create the new record. I think it is because the EmployeeID control is a combo box that displys the name but stores a number for the value.
(Like a pulldown menu.) Would that have...Leave a comment:
-
I figured out that the name of the combobox control was actually Combo9.
From what I understand, clicking on the button would open the from in add mode with all the data fields blank. I want to be able to copy the data from the EmployeeID field in the DIgitialEquipme nt form to the EmployeeID field in the newly opened form.
The frmDE prefix was used to keep these forms together in the database window, and I had several...Leave a comment:
-
This took care of 1 & 2:
Going to try fix step 3. The code opens the correct form in Add mode, but doesn't automatically fill in the EmpolyeeID field.Code:5. stItem = Me!Combo9.Column(0)
Leave a comment:
-
Error when opening a form with selection from combobox
Hi,
I am trying to create a command button that:
1. Opens the form as selected from a combobox,
2. Creates a new record, and
3. Inserts the EmployeeID automatically.
...Code:Dim stItem As String Dim stDocName As String Dim stLinkCriteria As String stItem = Forms!frmDigitalEquipment.CategoryList stDocName = "frmDE" & stItem -
Thanks Stewart,
I did not think of the Column qualifier. Thanks for your help.
dstormsLeave a comment:
-
Type mismatch error when selecting an item from a listbox
Hi,
I am trying to write code that opens a form using data highlighted from a listbox, but I keep getting Type Mismatch errors. I finally ran a test code and still got the error message.
I still can't figure out what is causing this error. Can anyone help?Code:Dim stItem As String stItem = Me.AssetList3(0) MsgBox ("'" & stItem & "'")
P.S. Removing... -
Stewart,
It turned out that I needed to write a more complex SQL statement, which I did get to work:
...Code:SQL = "UPDATE tblEmployees INNER JOIN " & Table & _ " ON tblEmployees.EmployeeID = " & Table & ".EmployeeID" & _ " SET " & Table & ".AssetNo = " & n & _ " WHERE (((tblEmployees.[EmployeeLeave a comment:
-
Thank you Stewart. That did fix the problem I described above. However, I've run into a different problem. When I try running the code I an error "3061", too few parameters: expected 1. I've determined that it has to do with the Table parameter. Either I'm not getting the value I want or the query the recordset calls is too complex to run. (It's a qruery filtering out one employee name from a previous union query.)
...Leave a comment:
-
Expected End of Statement thwarting attempt to update multiple tables
I'm trying to run an update query on multiple tables, and since Access doesn't allow me to update tables from a union query, I'm writing a module as a workaround. So I've set up a temporary recordest (rstName) from the union query (qryEqiupEmplOn ly), and construced a Do-Loop that updates the corresponding table.
Unfortunately the Update SQL code prompts a compile error "Expected End of statement" and highlights "rstName"... -
Linq,
Yes, that is weird. Thank you for responding.
So it's -1, not 1, that makes the check box work. I tried and, voila! the message displayed exactly as I intended.
But when the box is unchecked, I get a blank form. I don't want it to appear at all. Maybe a CloseForm WHERE checkbox=0?
Or could I create a VBA module that offers more control, and run it from the AutoExec macro? Some...Leave a comment:
-
Form does not display controls when opened through AutoExec macro
Something very strange going on here! Don't know where dstorms original post went! The post, in essence, stated that his form, containing a checkbox and a textbox, opened fine if opened independently, but opened without any controls showing if opened thru the Autoexec macro. The form was set up to open only Where ...checkbox = 1, but would open as stated above, with no controls showing, whether the box was checked or unchecked.
**************... -
dstorms replied to "Property let procedure..." error occurs when opening form determined by listbox datain AccessFishVal found the error. Now it works! Thanks to both for responding.Leave a comment:
-
dstorms started a topic "Property let procedure..." error occurs when opening form determined by listbox datain Access"Property let procedure..." error occurs when opening form determined by listbox data
Hi, I'm trying to create a form with a list box with 2 columns and a button that opens the selected record in the appropriate form. In the sample below, the first column lists the type of equipment (labeled Category) and the second column lists the equipment's serial number (SerialNo). The sub is supposed to use the Category data (say, Cameras) to select the appropriate form (in this case, AcctgDECameras) with the record containing the matching... -
Oh, Rabbit,
Google is my friend! After digging through several sites, I found the solution in an old post at another site. There I learned that this is how you set the recordset with a DAO reference:
After making the change, the sub worked flawlessly.Code:Set rs = DBEngine(0)(0).OpenRecordset("Table2", dbOpenDynaset)
Thank you for your time.Leave a comment:
-
No. Yes.
By commenting out just about every line, I discovered that I had misspelled "CurrentDb. "
Affter uncommenting all the lines I got another error message:
"Operation is not supported for this type of object."
I narrowed it down to this line:
I'll investigate this further....Code:rs.FindFirst "ComputerID =" & Me![ComputerID]
Leave a comment:
-
If it's not one thing, it's another. I added the reference to the DAO 3.6 library (ADO is unchecked), and now I'm getting an "object required" message. What's going on here?
At least I'm getting a crash course in Access and VBA programming!Leave a comment:
-
No it doesn't. Is there a way to find out?
I would prefer to use the first version if I could get it to work.
Also as an aside I've commented out the first two lines because when I executed the code I recieved a "compile error: user-defined type not defined." It runs fine without Dims set. Do you have any idea why that happens?
Doug...Leave a comment:
-
No, it's a long integer.
In Table 1 it is set by AutoNumber, and in Table 2 it is entered into a Long Integer. Would that have an effect?
I'm trying out this method instead:
1. Add a new field in Table 1 (Yes/No) labeled DoNotAddRecord
2. Run an update query to mark all previous records to Yes.
3. Modify the update query to limit it to the active record in the form
4. Create an append query to add...Leave a comment:
No activity results to display
Leave a comment: