?
Solved

VBA code for closing and opening form.

Posted on 2012-03-17
2
Medium Priority
?
279 Views
Last Modified: 2012-10-07
Access 2007  (db attached for reference if needed)

I've been working this final issue with this db for over a month now, and I'm at the end of my ropes.  I was putting this together for my wife who works from home and was hoping to have it completed by our anniversary which is on the 1st of April.

Someone please help me.  I'm NOT an acces guru.  Most of this DB I've put together from snippets here and there and alot of help from outside sources, mainly here.



I currently have an Notinlist event on cboScreenName on form [Call Details] which opens [Client Details] and then upon the closing of [Client Details], the user is brought back to [Call Details] at which time the [Call Details] is not being refreshed properly with cboScreenName being updated with the added new name, so the user ends up continuously being re-asked if they want to add the new name to the db unless they close [Call Details] completely and then reopen it.

The problem with my NotinList event refreshing is that the contents [cboScreenName] is dependent on [cboCompanyName], and THAT combo box gets its contents from vba.  Anyhow, its a bit of a confusing interdependancy the way that everything works but it DOES work, and well, as I'd like it to.

So I guess what I'm looking for, is some sort of code that I can put on the onclick event of the close button on [Client Details] which will completely close [Call Details] and then re-open with selections pre-populated in cboCompanyName and cboScreenName based on cboCompanyName and txtScreenName on [Client Details].

cboClientName and cboScreenName both grab their contents based on cboCompany afterUpdate event though, so not only does cboCompanyName need to pre-populate with the contents of cboCompanyName from [Client Details] but done so in a way that the afterUpdate event code still runs as if the user manually selected the company name in cboCompanyName
Database.mdb
0
Comment
Question by:JARichard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 37734015
Your DLookup syntax was incorrect.

The syntax should be:

DLookup("YourField", "YourTableOrQuery", "YourField---NotYourCombobox = '" & Something & "'")

The actual code should be:

    Result = DLookup("[Screen Name]", "tblClientInfo", _
             "[Screen Name]='" & NewData & "'")


(I replaced cboScreenName with [ScreenName])
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37734022
In other words you don't need to change your overall approach -- you just need to fix your DLookup statement in the Not In List event.

Please see JDettman's article here for a good explanation of DLookup and other domain functions:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question