Datasheet with entry at the top

[kmsperry]

I have a datasheet that has a list of interactions with a partner in reverse date order (Newest on top). I did this because I didn't want to scroll to the bottom each time to get the latest interaction. How can I enter a new interaction without having to scroll to the bottom of the datasheet each time. I tried putting unbound text boxes above the datasheet and putting a control button that has an onclick event that appends the data in the unbound boxes to the appropriate table but I'm getting hung up on the SQL Append and how to reference the data in the unbound boxes.

Can anyone help?

Thanks,

KMS

 

[boblarson]

The way you had it set up with the unbound boxes and SQL is about the only way I know of that you are going to get it to work. So, I think we need to work through any issues with that. Post more information like the query's SQL and the form name, the unbound control names.

 

[kmsperry]

The way you had it set up with the unbound boxes and SQL is about the only way I know of that you are going to get it to work. So, I think we need to work through any issues with that. Post more information like the query's SQL and the form name, the unbound control names.

I created a button with an onclick event that was a Macro that invoked an OpenQuery. The query referred to was as follows:

INSERT INTO Contact_Detail ( [Partners-Detail].[Master_ID], [Partners-Detail].[Date], [Partners-Detail].[Customer], [Partners-Detail].[Description], [Partners-Detail].[Employee] )
SELECT Contact_Detail.PartnerID, Contact_Detail.Date, Contact_Detail.Customer, Contact_Detail.Description, Contact_Detail.Employee
FROM Contact_Detail;

Contact_Detail is the table that has the transactions that are displayed in the datasheet.
Partners-Detail is the name of the form that contains the datasheet (which is actually on a tabbed page that has a subform on the page.)

The unbound text boxes are on the tabbed page above the datasheet and have the field names of Date, Customer, Description, and Employee.

When I tried the query (by pushing the button) I got no error and no new records appended to the Contact_Detail table.

Attached is what the form looks like.

Thanks,

KMS

 

[boblarson]

Well you need to actually insert the record information from the CONTROLS.

[FONT=Times New Roman][SIZE=3]INSERT INTO Partners-Detail ([Partners-Detail].[Date], [Partners-Detail].[Customer], [Partners-Detail].[Description], [Partners-Detail].[Employee] ) [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Values (Format([Forms]![YourFormNameHere]![DateTextBoxNameHere], "\#mm\/dd\/yyyy\#"), [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][Forms]![YourFormNameHere]![CustomerComboBoxNameHere], [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][Forms]![YourFormNameHere]![DescriptionTextBoxNameHere], [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][Forms]![YourFormNameHere]![EmployeeComboBoxNamehere])[/SIZE][/FONT]

So put that as the SQL of your query (changing the YourFormNameHere to the actual name of the form which the controls are on and changing the name of the controls to the actual names of the controls). If those controls are on a subform then we have to modify it further but I’m not going to at this time unless you say they are on a subform.

 

[kmsperry]

Well you need to actually insert the record information from the CONTROLS.

[FONT=Times New Roman][SIZE=3]INSERT INTO Partners-Detail ([Partners-Detail].[Date], [Partners-Detail].[Customer], [Partners-Detail].[Description], [Partners-Detail].[Employee] ) [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Values (Format([Forms]![YourFormNameHere]![DateTextBoxNameHere], "\#mm\/dd\/yyyy\#"), [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][Forms]![YourFormNameHere]![CustomerComboBoxNameHere], [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][Forms]![YourFormNameHere]![DescriptionTextBoxNameHere], [/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][Forms]![YourFormNameHere]![EmployeeComboBoxNamehere])[/SIZE][/FONT]

So put that as the SQL of your query (changing the YourFormNameHere to the actual name of the form which the controls are on and changing the name of the controls to the actual names of the controls). If those controls are on a subform then we have to modify it further but I’m not going to at this time unless you say they are on a subform.

I put the following code in the query.

INSERT INTO Contact_Detail ( Contact_Detail.Date, Contact_Detail.Customer, Contact_Detail.Description, Contact_Detail.Employee )
VALUES (Format(Forms![Partners-Detail]![Date_Ctrl],"\#mm\/dd\/yyyy\#"), [Forms]![Partners-Detail]![Customer_Ctrl], [Forms]![Partners-Detail]![Description_Ctrl], [Forms]![Partners-Detail]![Employee_Ctrl]);

I modified your SQL to insert into the Contact_Detail table since Partners-Detail is the form. When I executed the query I got the following error message. "The INSERT INTO statement contains the following unknown field name: 'Contact_Detail.Date'. Make sure you have typed the name correctly, and try the operation again."

Any further thoughts?

Thanks,

KMS

 

[boblarson]

Make sure that the date field is named date and if not change the SQL to use the correct field name. By the way, using DATE is a bad thing. Date is an Access Reserved word and using Access Reserved Words as field or object names can introduce problems.

And since you didn't post the code where you made the substitutions, you might have not included the square brackets around the Date field which is necessary since it is an Access Reserved Word.

 

[kmsperry]

Thanks Bob. Looks like I didn't have the syntax of the SQL quite right. Access didn't like the name of the table before the field name inside of the parenthesis. See below:

INSERT INTO Contact_Detail (PartnerID, [Date], Customer, Description, Employee )
VALUES (Forms![Partners-Detail]![Master_ID],Forms![Partners-Detail]![Date_Ctrl], [Forms]![Partners-Detail]![Customer_Ctrl], [Forms]![Partners-Detail]![Description_Ctrl], [Forms]![Partners-Detail]![Employee_Ctrl]);

I had tried this before but it didn't work becasue of the name of the date field that you mentioned in your last post. (it has to be in brackets)

Also, it didn't like the special date field formatting you had suggested. I removed it and it worked fine.

I'm up and running now.

Thanks for your help,

KMS